Skip to main content

PQL46 (PQL Function Library - CPM 4.6)

IS NULL

Applies to: CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4 CELONIS 4.5 CELONIS 4.6

Description

<input> IS NULL evaluates to true when the input is NULL, and false otherwise. The output can be negated by using <input> IS NOT NULL.

IS NULL should not be confused with the predicate function ISNULL, which returns an INT instead of a boolean.

IS [NOT] NULL can be used in the following contexts:

Syntax
input IS NULL
input IS NOT NULL
Examples

[1] IS NULL in a CASE WHEN context.

Query

Column1

CASE WHEN "Table1"."Column1" IS NULL THEN 0 ELSE "Table1"."Column2" END

Input

Output

Table1

Column1 : INT

Column2 : INT

1

11

null

13

5

15

Result

Column1 : INT

11

0

15

[2] IS NOT NULL in a FILTER context.

Query

Filter

FILTER "Table1"."Column1" IS NOT NULL;

Column1

"Table1"."Column2"

Input

Output

Table1

Column1 : INT

Column2 : INT

1

11

null

13

5

15

Result

Column1 : INT

11

15

[3] IS NOT NULL in a PU_SUM context, specifically the filter part of the Pull-Up-Function.

Query

Column1

"companyDetail"."companyCode"

Column2

PU_SUM ( "companyDetail" , "caseTable"."value" , NOT ( "companyDetail"."country" IS NOT NULL ) )

Input

Output

caseTable

caseId : INT

companyCode : STRING

value : INT

1

'001'

600

2

'001'

400

3

'001'

200

4

'002'

300

5

'002'

300

6

'003'

200

companyDetail

companyCode : STRING

country : STRING

'001'

'DE'

'002'

null

'003'

'US'

Foreign Keys

caseTable.companyCode

companyDetail.companyCode

Result

Column1 : STRING

Column2 : INT

'001'

null

'002'

600

'003'

null

See also: