3514448 - Isnull() function failing with syntax error in SAP SuccessFactors - People Analytics

In SAP SuccessFactors - People Analytics, 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\" ’".

• SAP SuccessFactors People Analytics
• SAP SuccessFactors HXM Suite

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.                                                                                               

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.

 

Examples with explanation:

Example 1:

To check for null values in a way that integrates seamlessly with logical functions like AND or OR as asked in the example, use IF to convert the ISNULL result into numbers (you can’t return direct Boolean values in IF function and if you try “true” | “false” it will consider it String and if use 1 or 0 it will consider them as number ). So here’s how you can make it work:

·         Boolean Conversion:
When you use IF(ISNULL([Field]), 1, 0) = 1, it returns a Boolean value—true if [Field] is null, and false otherwise. This approach produces a result that’s already in a Boolean format, allowing it to work smoothly with AND conditions without causing validation issues.

·         Using in Logical Expressions:
Once this expression returns a Boolean, it can integrate seamlessly with other logical operators. For example:

IF((1 > 3) AND (IF(ISNULL([Field]), 1, 0) = 1), 1, 0)

In this formula, (1 > 3) AND (IF(ISNULL([Field]), 1, 0) = 1) is valid because both conditions within AND are Boolean, which satisfies the function’s requirements.

Example of Correct Usage:

Example 1: In a full formula, the correct approach would look like this:

IF(1 > 3 AND IF(ISNULL([Employment#Job Information#Assignment Type]), 1, 0) = 1, 1, 0)

Here’s how it works:

• ISNULL Check: IF(ISNULL([Employment#Job Information#Assignment Type]), 1, 0) checks if [Assignment Type] is null. If null, it returns 1; otherwise, it returns 0.
• AND Condition: The expression 1 > 3 AND (result of IF condition) = 1 ensures that both parts of the AND condition are Boolean, making it valid.

 

Example 2:

 

IF([Employment#Job Information#Job Entry Date]<=TODATE(CONCAT("01/07/",

        TOTEXT(YEAR(CURRENTDATE()))),

        "DD/MM/YYYY"

) AND

IF(ISNULL([Employment#Job Information#Termination Date]),1, 0 ) = 1,

"OK" , "other")

Here’s how it works:

• ISNULL Check:  IF(ISNULL([Employment#Job Information#Termination Date]),1, 0 )

checks if [Termination Date] is null. If null, it returns 1; otherwise, it returns 0.

• AND Condition: The expression IF( [Employment#Job Information#Job Entry Date] <= TODATE(CONCAT("01/07/", TOTEXT(YEAR(CURRENTDATE()))), "DD/MM/YYYY")
  AND (result of ISNULL wrapped with IF()) = 1  , "OK", "other" )
  
  ensures that all parts of the AND condition are Boolean, making it valid following up with other AND/OR conditions.

Example 3:

IF(

IF(ISNULL([Recruiting#Candidate#Ethnicity]), 1,  0 ) = 1

OR ([Recruiting#Candidate#Ethnicity]="Unknown") OR ([Recruiting#Candidate#Ethnicity]="-99999"),

        "Unknown/Null",

        [Recruiting#Candidate#Ethnicity]

)

Here’s how it works:

• ISNULL Check:  IF(ISNULL([Recruiting#Candidate#Ethnicity]), 1, 0)

checks if [Ethnicity] is null. If null, it returns 1; otherwise, it returns 0.

• OR Condition: The expression (result of ISNULL wrapped with IF()) = 1 OR ([Recruiting#Candidate#Ethnicity]="Unknown") OR ([Recruiting#Candidate#Ethnicity]="-99999"),"Unknown/Null", [Recruiting#Candidate#Ethnicity]), ensures that all parts of the OR condition are Boolean, making it valid following up with other OR conditions.

• 2569847 - Where can you find SAC user assistance (help) to use, configure, and operate it more effectively?
• 2728183 - SAP Analytics Cloud (SAC) releases and release information
• 2511489 - Troubleshooting performance issues in SAP Analytics Cloud.
• Have a question? Ask it here and let our amazing SAP community help! Or reply and share your knowledge!
• 2487011 - What information do I need to provide when opening an incident for SAP Analytics Cloud?
• Search for SAP Analytics Cloud content using Google or Bing:
  • https://www.google.ca/search?q=site%3Ahttps%3A%2F%2Fapps.support.sap.com+SAP+Analytics+Cloud
  • https://www.bing.com/search?q=site%3Ahttps%3A%2F%2Fapps.support.sap.com+SAP+Analytics+Cloud
  • Note: Add relevant text or warning/error messages to the text search field to filter results.
• SAP Analytics Cloud Connection Guide
• Getting Started with SAP Analytics Cloud Expert Community page
• SAP Analytics Cloud Get More Help and SAP Support
• Need More Help? Contact Support or visit the solution finder today!

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

SAP Cloud for Planning, sc4p, c4p, cforp, cloudforplanning, Cloud for Analytics, Cloud4Analytics, CloudforAnalytics, Cloud 4 Planning, BOC, SAPBusinessObjectsCloud, BusinessObjectsCloud, BOBJcloud, BOCloud., SAC, SAP AC, Cloud-Analytics, CloudAnalytics, SAPCloudAnalytics,Error, Issue, System, Data, User, Unable, Access, Connection, Sac, Connector, Live, Acquisition, Up, Set, setup, Model, BW, Connect, Story, Tenant, Import, Failed, Using, Working, SAML, SSO, sapanalyticscloud, sap analytical cloud, sap analytical cloud, SAC, sap analyst cloud, connected, failure, stopped, sap analyst cloud  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

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