Where clause examples
A where clause is used to filter information from an SData feed. The SData query language is
used to express filtering conditions with the where query parameter. Information from an SData
feed is subject to security, so users can only see records for which they have permission to
view.
Note: Creating a where clause is recommended for Advanced users only.
The syntax for creating a where clause is very specific. If the syntax is incorrect, you may see undesirable results. For more information, see Creating an SData Where clause
Commonly Used Filters
For the following examples, assume that you selected the Account resource.
| Data Filter Condition | Where Clause Operator | Example | Result |
|---|---|---|---|
| equal | eq | CreditRating eq ‘Good’ | A list of accounts where the credit rating is equal to "Good". |
| compare two columns | CreateUser eq ModifyUser | A list of all accounts that were last modified by the same user who created the account. | |
| equals a date | year(CreateDate) eq 2008 and month(CreateDate) eq 06 and day(CreateDate) eq 14 | A list of all accounts created on the specified day, month, and year. | |
| not equal | ne | Industry ne ‘Services/Consulting’ | A list of accounts that are not in the Services/Consulting industry. |
| less than | lt | Revenue lt 5000000 | A list of accounts with revenue less than 5 million. |
| less than or equal to | le | LastHistoryDate le @2012-01-01T00:00:01@ | A list of accounts where the most recent history item occurred on or before January 1st 2013. |
| greater than | gt | Employees gt 100 | A list of accounts with more than 100 employees. |
| greater than or equal to | ge | CreateDate ge @2013-01-01T00:00:00+00:01 | A list of all accounts created on or after January 1st 2013. |
| within a range | between | ShippingAddress.PostalCode between 60650 and 60699 | A list of all accounts with a postal code between 60650 and 60699. |
| In the set | in | CreditRating in (‘Good’, ‘Fair’, ‘Poor’) | A list of all accounts with a credit rating of 'Good' or 'Fair' or 'Poor'. |
| Not in the set | not in | CreditRating not in (‘Good’, ‘Fair’, ‘Poor’) | A list of all accounts with a credit rating that is not 'Good' or 'Fair' or 'Poor'. |
| contains | like | BusinessDescription like ‘%subsidiary%’ | A list of accounts that contain 'subsidiary' in their description of business. |
| starts with (first character) | left(<column>,1) between | left(AccountName,1) between ‘A’ and ‘C’ | A list of all accounts with a name that starts with 'A', 'B', or 'C'. |
| starts with (first 2 characters) | left(<column>,2) eq | left(AccountName,2) eq ‘Ab’ | A list of all accounts with a name that starts with 'Ab'. |
| starts with (first 3 characters) | left(<column>,3) eq | left(MainPhone,3) eq 312 | A list of all accounts with main phone numbers in the 312 area code. |
| occurred within the last X days | dateAdd(<column>,X) ge | dateAdd(ModifyDate, 7) ge current_timestamp | A list of all accounts modified in the last 7 days. |
If the SData feed widget does not return data as expected, some of the common mistakes seen
are:
- Ensure that the where clause contains the vertical quotes (' '), and not the slanted quotes (‘ ’).
- Ensure that the column name is correct. Column names are case sensitive and are based on the underlying name, not the 'pretty' name that appears in Infor CRM views. For example, AccountName, not Account or Account Name.