Skip to main content

Celonis Product Documentation

ISNULL
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: