3. Conditions

3. Conditions


We can use WHERE condition to filter the records. 

SELECT wo.WORKORDERID "Request ID",
       std.STATUSNAME "Request Status" FROM WorkOrder wo
LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID
LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID
WHERE std.STATUSNAME = 'open'

  


Operators in WHERE clause:

 

= Equal

<> Not equal. Note: In some versions of SQL this operator may be written as !=

> Greater than

< Less than

>= Greater than or equal

<= Less than or equal



AND and OR Operators:

 

The WHERE clause can be combined with AND and OR operators.

The AND and OR operators are used to filter records based on more than one condition:


  • The AND operator displays a record if all the conditions separated by AND are TRUE.


                    (cd.CATEGORYNAME = 'Hardware'  AND  std.STATUSNAME = 'open')



  • The OR operator displays a record if any of the conditions separated by OR is TRUE.


                  (cd.CATEGORYNAME = 'Hardware'  OR  std.STATUSNAME = 'open')


Combination of AND and OR:

((cd.CATEGORYNAME = 'Hardware' AND std.STATUSNAME = 'open') OR (ti.FIRST_NAME = 'Howard'))




LIKE Operator 


The LIKE operator can be used in the conditional selection of the where clause. The percent sign "%" can be used as a wild card to match any possible character that might appear before or after the characters specified. For example: 

(cd.CATEGORYNAME LIKE '%Hardware%')

WHERE cd.CATEGORYNAME LIKE 'a%'  - Finds any values that start with "a" 
WHERE cd.CATEGORYNAME LIKE '%a'  - Finds any values that end with "a"
WHERE cd.CATEGORYNAME LIKE '%are%'  - Finds any values that have "are" in any position


IN Operator

The IN operator allows you to specify multiple values in a WHERE clause.

cd.CATEGORYNAME IN ('hardware','printer','software')




          New to M365 Manager Plus?
          New to M365 Manager Plus?
            New to RecoveryManager Plus?
            New to RecoveryManager Plus?
              New to Exchange Reporter Plus?
              New to Exchange Reporter Plus?
                New to SharePoint Manager Plus?
                New to SharePoint Manager Plus?
                  See all integrations
                  New to ADManager Plus?
                  See all integrations
                  New to ADManager Plus?

                    New to ADSelfService Plus?

                    Start your free trial
                    Start your free trial

                      • Related Articles

                      • Widget-Based Solution for Importing General Tasks into ServiceDesk Plus

                        Overview: ServiceDesk Plus does not provide a native XLS or XLSX import option for General Tasks in the same way users often expect for bulk spreadsheet uploads. To address this requirement, this widget-based solution can be used to import General ...

                      • Two-Way Integration Between ManageEngine ServiceDesk Plus (On-Premise) and JIRA

                        Overview ManageEngine ServiceDesk Plus (SDP) On-Premise ships with a native Automation engine — Custom Triggers, Business Rules, Custom Functions, and outbound Webhooks — that is powerful enough to build real two-way integrations with third-party ...

                      • How to restrict adding of notes by users

                        1. Go to Admin > Page Scripts: 2. Create New Rule > configure if it needs specifically for Technicians or Requesters or All Users and set the Event > Conditions as per your requirement 3. Under Actions > Execute Script > Make use of the below script: ...

                      • Hide Rocket Notification Icon

                         Please follow the below mentioned from build 10600 and above: 1. Go to Admin > Page Scripts > click 'New Rule'. Give the rule a suitable name. 2. Define the conditions as per your requirement. 3. Under Actions click 'Write Custom Script' and paste ...

                      • 1. Query Basics

                        Basic Query: SELECT wo.WORKORDERID AS "Request ID", wo.TITLE AS "Subject", aau.FIRST_NAME AS "Requester", ti.FIRST_NAME AS "Technician", cd.CATEGORYNAME AS "Category", std.STATUSNAME AS "Request Status" FROM WorkOrder wo LEFT JOIN SDUser sdu ON ...
                      Related Products