VERIFIED SOLUTION i
X

How to use NULL within Data Source SQL in Confirm

UPDATED: September 27, 2017


SQL that involves the querying of NULLs must correctly test for those NULLs. A NULL cannot be equal (=), not equal (!= |<>), to anything or NOT LIKE a value - it has to be tested for especially with either an "IS NULL" or "IS NOT NULL" clause. 

For example a column named complete_flag in the table insp_route_feat has distinct values of 'C', 'N' and NULL entries. The user writing the query wishes  to retrieve rows where complete_flag is either NULL or 'N'; they do not need the completed ones ('C'). Both of these queries will work:

(insp_route_feat.complete_flag <> 'C' OR
insp_route_feat.complete_flag IS NULL)


-OR- Similarly:

(insp_route_feat.complete_flag = 'N' OR
insp_route_feat.complete_flag IS NULL)

 

Environment Details

Product Feature: Ad-hoc Reporting
 

Downloads

  • No Downloads