SAP Knowledge Base Article - Public

2151847 - Query is returning duplicated or missing records - OData API

Symptom

When you run an OData query, currently "order by" clause if not explicitly specified, application will not apply any default 'order by' clause into the query. Without 'order by' clause the query result in each page is not certain so you could get duplicate/Missing records in different query pages.

If no sorting criteria is used in then the result might not be unique in the pages and this leads to wrong results by the query. The root cause of this problems seems to be a unstable sorting during paging of the API. 

Image/data in this KBA is from SAP internal systems, sample data, or demo systems. Any resemblance to real data is purely coincidental.

Environment

  • SuccessFactors OData API.

Resolution

Add $orderBy=code,effectiveStartDate to the OData query.

If duplicated/missing records/data are seen during pagination, 'order by' Clause should be the solution to resolve this issue.

You should use all business keys defined for an object in OrderBy clause to avoid duplicate/Missing record, remember to also include business keys from child objects/navigations

  1. Is this same issue likely to occur for any other SFOdata objects for which Boomi sends similar queries?
    Answer : As a best practice it is always good to Sort the fields on "unique code" for that entity.
  2. Should we recommend to make changes in Boomi to add the sorting feature as work around into the query for every SFOdata object currently being queried for, even though the same problem has not been reported for any of these other objects?
    Answer: As a best practice yes.

There are currently 2 types of Server Pagination:

  • Cursor-Based Pagination;
  • Snapshot-Based Pagination.

For additional information on each one, refer to Workbook: SAP SuccessFactors HCM Suite OData API: Developer Guide page 45.

Pagination in HCI:

HCI.png

Pagination in Integration Center:

IntegrationCenter pag.png

Keywords

Duplicate, SFOdata Duplicates, Order By, Pagination, Odata query is returning duplicated or missing records , KBA , LOD-SF-INT-ODATA , OData API Framework , LOD-SF-INT , Integrations , LOD-SF-INT-API , API & Adhoc API Framework , Problem

Product

SAP SuccessFactors HXM Core all versions