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?

                    New to ADSelfService Plus?

                    Start your free trial

                      • Related Articles

                      • 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 ...

                      • How to Automatically Associate a Ticket to a Project using PrjID based on Ticket Template

                        This post describes the use of a sample python script you to associate a Project automatically to a ticket during its creation/edit based on conditions, such as template matching. This script can be configured under Custom Triggers and you will find ...

                      • AWS Cloudwatch Metric Alarm Integration

                        Requirement  :  AWS Cloudwatch Metric Alarm Integration Steps :  --> Download the attached file AwsCWAFiles.zip and extract it --> Copy file AppIntegrations_Handler_Custom.xml to sdp_home\integration\conf  folder ( If the file is already available ...

                      • ADMP Integration - How to automate AD user creation

                        This post described the use of a python script to automate AD user creation from request. This script can be configured under Request custom triggers and you will find more information about this here. Integrating ServiceDesk Plus with ADManager Plus ...

                      • FAFR to set priority based on other change fields

                        Requirement: Set priority based on 3 change fields.  i.e Change Additional field - "Coverage", "Impact" and "Urgency". Steps to configure: 1.  Goto Admin > Change Templates > Field and Form Rules > On Field Change:  Urgency. 2.  Set Conditions as:  ...

                        Related Products