2884860 - SFAPI Domain Error! INVALID_SFQL: Error: IN operator for <field/property> must not have more than 1000 expressions

Symptom

Getting below error when executing an SFAPI Query Operation (in this example compoundEmployee API query is used)

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

API Error Response :

<faultstring>SFAPI Domain Error!</faultstring>

<ns2:SFWebServiceFault xmlns:ns2="urn:fault.sfapi.successfactors.com" xmlns="urn:sfobject.sfapi.successfactors.com"> <ns2:errorCode>INVALID_SFQL</ns2:errorCode>

Message : Error IN operator for <field/property> must not have more than 1000 expressions

Error SFAPI.JPG

Environment

SAP Successfactors HXM Suite

Reproducing the Issue

1. Execute SFAPI Query with criteria in WHERE statement exceeding 1000 clauses. e.g. below

SELECT address_information,associated_employee_information,compensation_information,email_information,employment_information,job_information,national_id_card,paycompensation_non_recurring,paycompensation_recurring,payment_information,person,personal_information,phone_information from CompoundEmployee

WHERE person_id_external IN ('0001','0002','0003',......'nth',)

</queryString>

*nth= more than 1000 value expressions.

2. See Error

Cause

The WHERE clause in SFQL is limited up to 200 OR/AND subclauses. Due to the Oracle maximum number (1000) of expressions in a list (ORA-01795), SFQL does not accept an IN clause that exceeds more than 1000 expressions.

See SFAPI Developer Guide :SFAPI Best Practice

Resolution

1. Reduce the number of filter value in the WHERE Expression up to 1000 values only. The limit is 1000. Although this type of query is not recommend as it is not efficient to be putting the filter criteria one by one especially for person_id_external. Instead see #2.

WHERE person_id_external IN ('0001','0002','0003',......'999',)

</queryString>

2. Instead of filtering with a specific field and assigning specific values in the criteria e.g. person_id_external(employee id) use other filter like, country filter, department filter, this way you need not provide the actual person_id_external in your process' query.

You can limit the number of selected employees per run by using selection parameters such as company, country, pay group, or employee class. Instead of using person_id_external as your filter.

See Guide : CompoundEmployee API Threshold, Limitation, Best Practice

Keywords

SFAPI Domain Error!, InvalidSFQL, 1000 WHERE clause in SFQL is limited up to 200 OR/AND subclauses, CompoundEmployee API Threshold, Limitation, Best Practice, must not have more than 1000 expressions , KBA , LOD-SF-INT-API , API & Adhoc API Framework , LOD-SF-INT , Integrations , Problem

Product

SAP SuccessFactors HCM Core all versions