SAP Knowledge Base Article - Public

2325691 - Odata Query with Top-Skip combination returns unexpected results when $orderBy

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 - 

/odata/v2/EmpJobRelationships?$format=json&$select=relUserId,relationshipType,userId,relationshipTypeNav&$expand=relationshipTypeNav&$orderby=relationshipType%20asc&$filter=%28%28lastModifiedDateTime%20gt%20datetimeoffset%272016-05-24T08:32:27Z%27%29%20or%20%28startDate%20gt%20datetime%272016-05-24T08:32:27Z%27%20and%20startDate%20le%20datetime%272016-05-24T08:47:24Z%27%29%29&$top=800&$skip=200

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

  1. Doing an $orderBy while data is changing may fetch inconsistent results.
  2. 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

Product

SAP SuccessFactors HCM Core all versions