Happy Business Starts Here

ZOQL Query Behaviour with NULL values

Highlighted
Zuora Support

ZOQL Query Behaviour with NULL values

Issue: Null values are not returned when we use  “!=” condition in the "/v1/action/query"

 

For example:

WHERE Id = “1111111” and Customfield__c != 'SUSPENDED'

 

The above condition will return all the values other than SUSPENDED but the Null records are excluded from the result.

 

Solution:

 

As the Best practice, when "!=" statement is used it is always recommended to include OR condition filtering the NULL values.

 

If we are using ZOQL export, the  Filter_Statements with Paranthesis can be used to filter the condition -  where Id = "1111111"
and (Customfield__c != 'SUSPENDED' OR Customfield__c != NULL)

 

It is quite different with /action/query, As given in these Query_Statement_Examples.

It should be something like this:

Select Id, Customfield__c from Account where  Customfield__c = NULL AND Id = "1111111" 
OR  Customfield__c != 'SUSPENDED'  AND Id = "1111111"


In this case, Customfield__c = NULL AND Id = "1111111" as well as Customfield__c != 'SUSPENDED'  AND Id = "1111111" are evaluated first.