Symptom
You notice that records of few employees are missing when an Odata query is run with $orderBy.
or
you notice that not all the expected results return when the combination of Top-Skip is used with $orderBy.
Environment
Successfactors
Reproducing the Issue
You may consider the below example -
Execute the below Query -
Data is related to the userId 12345 is present in the above response. (API response is not shared here)
Observation:
data is present with ; top=800&$skip=200 ; indicates that data lies between records 201 to 1000.
data is absent with ; top=600&$skip=200 ; indicates that data doesnt exist between records 201 to 800.
From the above, We can conclude that data lies between records 800 to 1000.
To confirm, a skip 800 and top 200 can be done. However, the record of the above user is not fetched.)
Cause
- Doing an $orderBy while data is changing may fetch inconsistent results.
- Please ensure all the business keys are used in $orderBy clause. If you do not wish to use orderBy clause, make sure you use all business keys in select statement. Example from this case: userId is missing in the select statement of the Query.
Resolution
Please also add userId in the select statement of the query. Example below:
odata/v2/EmpJobRelationships?$format=json&$select=relUserId,relationshipType,userId,relationshipTypeNav&$expand=relationshipTypeNav&$orderby=relationshipType+asc ,userId&$filter=((lastModifiedDateTime+gt+datetimeoffset'2016-05-24T08:32:27Z')+or+(startDate+gt+datetime'2016-05-24T08:32:27Z'+and+startDate+le+datetime'2016-05-24T08:47:24Z'))&$top=450&$skip=50
Keywords
Odata API top/skip issue OrderBy fetches incorrect results. , KBA , LOD-SF-INT , Integrations , LOD-SF-INT-API , API & Adhoc API Framework , Problem