SAP Knowledge Base Article - Public

3389932 - Available filters on OData API - SuccessFactors

Symptom

You wish to know all the available filters when using OData API.

Environment

SAP SuccessFactors HXM Suite

Resolution

There are a few types of filters on OData. They have been grouped on the following topics: Logical, Arithmetic, Grouping, Customized, String Function and Time-Based.

Logical Operators

LOGICAL OPERATORDESCRIPTIONEXAMPLE
eqEqualFinds a User entity whose hr/username is cgrant (hr is the navigation property): https://<API-Server>/odata/v2/User?$filter=hr/username eq 'cgrant'. Finds a User Entity whose username property is cgrant: https://<API-Server>/odata/v2/User?$filter=username eq 'cgrant' .
neNot equalhttps://<API-Server>/odata/v2/User?$filter=hr/username ne 'London'
gtGreater thanFinds a PickListLabel Entity whose id is greater than 2000:https://<API-Server>/odata/v2/PicklistLabel?$filter=id gt 20000
geGreater than or equalhttps://<API-Server>/odata/v2/PicklistLabel?$filter=id ge 10
ltLess thanhttps://<API-Server>/odata/v2/PicklistLabel?$filter=id lt 20
leLess than or equalhttps://<API-Server>/odata/v2/PicklistLabel?$filter=id le 100
andLogical andhttps://<API-Server>/odata/v2/PicklistLabel?$filter=id le 1005 and id gt 1000to find PicklistLabel whose id is within range [1000,1005]
orLogical orhttps://<API-Server>/odata/v2/PicklistLabel?$filter=id le 3.5 or id gt 200
notLogical negationhttps://<API-Server>/odata/v2/User?$filter=not endswith(username,'grant')
 
 
Note: There's a limit to the number of expressions you can use in a single query. This number varies from server to server. When the limit is reached, an error occurs. To solve this problem, reduce the number of expressions in your query.

Arithmetic Operators

ARITHMETIC OPERATORDESCRIPTIONEXAMPLE
addAdditionhttps://<API-Server>/odata/v2/PicklistLabel?$filter=id add 5 gt 10
subSubtractionhttps://<API-Server>/odata/v2/PicklistLabel?$filter=id sub 5 gt 10
mulMultiplicationhttps://<API-Server>/odata/v2/PicklistLabel?$filter=id mul 2 gt 2000
divDivisionhttps://<API-Server>/odata/v2/PicklistLabel?$filter=id div 2 gt 4
modModulohttps://<API-Server>/odata/v2/PicklistLabel?$filter=id mod 2 eq 0
 
 

Grouping Operator

GROUPING OPERATORDESCRIPTIONEXAMPLE
()Precedence grouping.https://<API-Server>/odata/v2/PicklistLabel?$filter=(id sub 5) gt 10
 
 

Customized Operators

CUSTOMIZED OPERATORDESCRIPTIONEXAMPLE
inIn clauseIdentifies User entities whose userId is equal to one specified by an in clause:https://<API-Server>/odata/v2/User?$filter=userId in 'ctse1','mhuang1','flynch1'&$select=username,userId
Note

Due to the limit of number of expressions (1000) in a list, OData API doesn’t accept an IN clause exceeding 1000 expressions in $filter.

likeLike clause

Identifies User entities whose userId property cgrant may be retrieved with a like clause:

https://<API-Server>/odata/v2/User?$filter=userId like ’cgrant’

Identifies User entities whose userId property is equivalent to cgrant:

$filter=userId eq 'cgrant’

Identifies User entities whose userId property cgrant may be retrieved with a like clause:

https://<API-Server>/odata/v2/User?$filter=userId like ’cgrant%’

Identifies User entities whose userId property starts with cgrant:

$filter=startswith(userId, 'cgrant’)

Identifies User entities whose userId property ends with cgrant:

https://<API-Server>/odata/v2/User?$filter=userId like ’%cgrant’

This is equivalent to $filter=endswith(userId, 'cgrant’)

Identifies User entities whose userId property contains string cgrant.

https://<API-Server>/odata/v2/User?$filter=userId like ’%cgrant%’

Identifies User entities whose userId property contains string cgrant and is case insensitive.

https://<API-Server>/odata/v2/User?$filter=tolower(userId) like ’%cgrant%’

https://<API-Server>/odata/v2/User?$filter=toupper(userId) like ’%cgrant%’

Identifies User entities whose userId is like cgrant and unions with username like cgrant.

https://<API-Server>/odata/v2/User?$filter=toupper(userId) like ’%cgrant%’ or tolower(username) like '%cgrant%’

Identifies User entities whose userId is like cgrant and intersects with username like cgrant.

https://<API-Server>/odata/v2/User?$filter=toupper(userId) like ’%cgrant%’ and tolower(username) like '%cgrant%’

 
 

String Functions

In addition to operators, a set of functions is also defined for use with the $filter query option. The following table lists the available functions.

Note: ISNULL or COALESCE operators are not defined. Instead, there’s a null literal that can be used in comparisons. If your query contains a single quote, it must be escaped by another one if it appears with single-quoted string.

STRING FUNCTIONEXAMPLE
bool endswith(string p0, string p1)

Finds all usernames that end with Futterkiste:

https://<API-Server>/odata/v2/User?$filter=endswith(username, 'Futterkiste')

bool startswith(string p0, string p1)

Finds all usernames that start with Alfr:

https://<API-Server>/odata/v2/User?$filter=startswith(username, 'Alfr')

bool substringof(string p0, string p1)

Case-insensitive search for username field:

https://<API-Server>/odata/v2/User?$filter=substringof('futter',tolower(username)) eq true

string tolower(string p0)

Compare username in lower case to the literal value futterkiste :

https://<API-Server>/odata/v2/User?$filter=tolower(username) eq 'futterkiste'

string toupper(string p0)

Compare username in upper case to the literal value 'FUTTERKISTE' :

https://<API-Server>/odata/v2/User?$filter=toupper(username) eq 'FUTTERKISTE'

string trim(string p0)

Trim leading and trailing whitespaces before comparing values:

https://<API-Server>/odata/v2/User?$filter=trim(username) eq 'Futterkiste'

 
 

Time-Based

QUERY EXAMPLE
https://<API-Server>/odata/v2/User?$format=json&$select=lastModified&$filter=lastModified%20ge%20datetime%272003-12-18T17:19:28%27&$top=200

See Also

SAP SuccessFactors API Reference Guide (OData V2)

Keywords

filter, odata api, Logical Operators, Arithmetic Operators, Grouping Operator, Customized Operators, String Functions , KBA , LOD-SF-INT-ODATA , OData API Framework , How To

Product

SAP SuccessFactors HCM Suite all versions