Skip to main content

PQL46 (PQL Function Library - CPM 4.6)

ISNULL

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

Description

The ISNULL function returns an INT which indicates whether the input value is NULL. If the input value is NULL, 1 is returned, and 0 otherwise.

ISNULL should not be confused with the predicate operator IS NULL which returns a boolean.

ISNULL can be applied to any data type.

Typically, ISNULL is used inside a condition, for example in a CASE WHEN or FILTER statement. However, as the function returns an INT , it is not restricted to be used with those statements.

In order to map NULL values to a different value, COALESCE or REMAP_VALUES can also be used instead of a CASE WHEN combined with ISNULL.

Syntax
ISNULL ( table.column ) 
Examples

[1] Return the result of the ISNULL operator for a STRING column and use it inside a CASE WHEN condition.

Query

Column1

ISNULL ( "Table1"."Country" )

Column2

CASE WHEN ( ISNULL ( "Table1"."Country" ) = 1 ) THEN '-' ELSE "Table1"."Country" END

Input

Output

Table1

Country : STRING

'US'

'DE'

null

'IT'

'DE'

null

'FR'

Result

Column1 : INT

Column2 : STRING

0

'US'

0

'DE'

1

'-'

0

'IT'

0

'DE'

1

'-'

0

'FR'

[2] Return the result of the ISNULL operator for a INT column and use it inside a CASE WHEN condition.

Query

Column1

ISNULL ( "Table1"."IntColumn" )

Column2

CASE WHEN ( ISNULL ( "Table1"."IntColumn" ) = 1 ) THEN 0 ELSE "Table1"."IntColumn" END

Input

Output

Table1

IntColumn : INT

1

0

10

13

null

null

-5

Result

Column1 : INT

Column2 : INT

0

1

0

0

0

10

0

13

1

0

1

0

0

-5

See also: