SAP Knowledge Base Article - Public

3514448 - Isnull() function not working - SAP Analytics Cloud

Symptom

In Query Builder, Calculated Column created using functions ISNULL, LIKE, and NOT which return Boolean type will throw an error as: ‘ Caught exception : exception 1000013: incorrect syntax near \"IS\" ’".

Environment

SAP Analytics Cloud embedded in SAP SuccessFactors

Cause

ISNULL, LIKE, and NOT are special predicates that require a CASE WHEN/IF statement to function correctly. 

Currently, the error is thrown from the Database layer failing to handle the return type Boolean in the dynamic calculations.

Resolution

In order to use ISNULL, NOT, and LIKE, users need to wrap these predicates within an IF() function.

We will provide better experience with validation checks to avoid runtime error messages in upcoming releases.

Example:

Not working: ISNULL(AGENCY_ID)
Working: IF(ISNULL(AGENCY_ID), 1, 0) 

See Also

Your feedback is important to help us improve our knowledge base.

Keywords

SAP Analytics Cloud, SAC, sapanalyticscloud, kba, note, help, knowledge, embedded, analytics, ISNULL, NOT, LIKE, INA Calls, MDS , KBA , LOD-ANA-OEM , Embedded usage of SAP Analytics Cloud , LOD-SF-ANA-SAC , Stories in People Analytics , Known Error

Product

SAP Analytics Cloud all versions ; SAP SuccessFactors HCM Core all versions