Overview
Now that you’ve learned some basic filter clause operators from this knowledge article: https://silverband.atlassian.net/wiki/spaces/DPG/pages/1457127488, you can add more complex filters by using the operators mentioned in this article. The following operators are mostly used to compare two fields.
Navigation
Table of Contents | ||||||
---|---|---|---|---|---|---|
|
Comparison Operators
Like
Like is a wildcard value. This will return values that contain a pattern match (partial keyword matching) with the same case as set in the value.
Example 1
“First Name” Like Br (will return Brianna, Brian, Bridget) but not (brian, brianna, bridget)
Example 2
“UnitCode” Like ACC (will return all unit codes with “ACC” keyword, like ACCOUNTING 101, etc.)
Like Case Insensitive
If you want a LIKE Operator which would not mind the case on how the data are coded as long as it contains/matches the value set, then use this operator. This will return values that contain a pattern match (partial keyword matching) regardless of the letter case.
Example 1
“First Name” Like Br (will return Brianna, Brian, Bridget) as well as (brian, brianna, bridget)
Example 2
“UnitCode” Like ACC (will return all unit codes with “Acc” keyword, like Accounting101, etc.)
Not Like
Displays records if a condition is not TRUE or doesn’t match the selected pattern match.
Example 1
“Surname” NOT LIKE Smith (will return other surnames like: Jones, Brown, Davies except for Smith)
Example 2
“Student Number” NOT LIKE A (will return all other student numbers except for the ones that starts with A)
Is
Checks whether a value IS NULL. The IS NULL returns TRUE if the expression IS NULL, otherwise, it returns FALSE.
Example 1
“Grade Description ” IS NULL (will return true if the field is NULL, else false)
Is Not
Checks whether a value IS NOT NULL. The IS NOT NULL returns TRUE if the expression is NOT NULL, otherwise, it returns FALSE.
Example 1
“Invoice Id” IS NOT NULL (will return true if the field is NOT NULL, else false)
Field Equals (=)
Compares the contents of one field if the same or equal to another field.
Example 1
“Country” Field Equals (=) “Billing Country” will return data which has the same country and billing country information
Sample Output
Field Not Equals !=
Will return data or information with a field not equal to the other field.
Example 1
“Country” Field Not Equals (=) “Billing Country” will return data which has a country not equal or the same as the billing country
Sample Output
Field Greater Than (>)
Will return data or information with a field greater than the other field.
Example 1
“Withdrawal Date” Field Greater Than (>) “Dest Census Date will return data which has a withdrawal date greater than the Dest Census Date.
Sample Output
Field Less Than (<)
Will return data or information with a field less than the other field.
Example 1
“Amount Paid” Field Less Than (<) “Amount Due” will return data which has an amount paid less than the amount due.
Sample Output
Field Greater Than Equal To (>=)
Will return data or information with a field greater or equal to the other field. The difference between this and the Field Greater Than (>) operator is that this would include the field which is also equal to the other field.
Example 1
“Withdrawal Date” Field Greater Than Equal To (>=) “Dest Census Date will return data which has a withdrawal date greater than or equal to the Dest Census Date.
Sample Output
Field Less Than Equal To (<=)
Will return data or information with a field less than or equal to the other field. The difference between this and the Field Less Than (>) operator is that this would include the field which is also equal to the other field.
Example 1
“Amount Paid” Field Less Than Equal To (<=) “Amount Due” will return data which has an amount paid less than or equal to the amount due.
Sample Output
Advanced Report Builder | Order By Clauses | Alternative Data Formats | Bulk Student Select | Complex Filter Clause Operators | Nested Operators Using Brackets | User-Defined Parameters | Summarise Data | Generic Report Query