Skip to main content

TO_NULLABLE_INT

Syntax

TO_NULLABLE_INT ( condition )

Null handling

If the input value is NULL, the output value is NULL as well.

Example

[1]

Evaluate a condition and convert to int. Note the NULL output for the comparison with NULL.

Query

Column1 

TO_NULLABLE_INT ( "Table"."Column1" > 5 )

Input

Output

Table

Column1 : int

4

5

6

9

null

Result

Column1 : int

0

0

1

1

null

[2]

Comparing TO_NULLABLE_INT to an equivalent CASE WHEN statement producing the same output.

Query

Column1 

TO_NULLABLE_INT ( "Table"."Column1" > 5 )

Column2 

CASE WHEN "Table"."Column1" > 5 THEN 1 WHEN NOT ( "Table"."Column1" > 5 ) THEN 0 ELSE NULL END

Input

Output

Table

Column1 : int

4

5

6

9

null

Result

Column1 : int

Column2 : int

0

0

0

0

1

1

1

1

null

null

[3]

An example showing the use of logical operators in the condition. The result shows how the NULL handling of the AND and the OR operators is propagated to the TO_NULLABLE_INT operator.

Query

Column1 

"Table"."Column1"

Column2 

"Table"."Column2"

Column3 

TO_NULLABLE_INT ( "Table"."Column1" = 'TRUE' AND "Table"."Column2" = 'TRUE' )

Column4 

TO_NULLABLE_INT ( "Table"."Column1" = 'TRUE' OR "Table"."Column2" = 'TRUE' )

Input

Output

Table

Column1 : string

Column2 : string

'FALSE'

'FALSE'

'FALSE'

'TRUE'

'FALSE'

null

'TRUE'

'FALSE'

'TRUE'

'TRUE'

'TRUE'

null

null

'FALSE'

null

'TRUE'

null

null

Result

Column1 : string

Column2 : string

Column3 : int

Column4 : int

'FALSE'

'FALSE'

0

0

'FALSE'

'TRUE'

0

1

'FALSE'

null

0

null

'TRUE'

'FALSE'

0

1

'TRUE'

'TRUE'

1

1

'TRUE'

null

null

1

null

'FALSE'

0

null

null

'TRUE'

null

1

null

null

null

null

[4]

An example with a condition combining two tables connected by a 1:N relationship.

Query

Column1 

"Cases"."CaseID"

Column2 

TO_NULLABLE_INT ( "Cases"."CasePriority" = 'high' AND "Activities"."activityType" = 'automated' )

Input

Output

Activities

caseID : int

activityName : string

activityType : string

1

'A'

'automated'

1

'B'

'automated'

1

'C'

'automated'

2

'A'

'automated'

2

'B'

'automated'

2

'D'

'manual'

3

'A'

'automated'

3

'D'

'manual'

3

'E'

null

Cases

caseID : int

caseName : string

casePriority : string

1

'Case one'

'low'

2

'Case Two'

'high'

3

'Case Three'

'high'

Foreign Keys

Activities.CaseID

Cases.CaseID

Result

Column1 : int

Column2 : int

1

0

1

0

1

0

2

1

2

1

2

0

3

1

3

0

3

null

[5]

An example showing the use of TO_NULLABLE_INT for counting automated activities for each case.

Query

Column1 

"Cases"."caseID"

Column2 

SUM ( TO_NULLABLE_INT ( "Activities"."activityType" = 'automated' ) )

Input

Output

Activities

caseID : int

activityName : string

activityType : string

1

'A'

'automated'

1

'B'

'automated'

1

'C'

'automated'

2

'A'

'automated'

2

'B'

'automated'

2

'D'

'manual'

3

'A'

'automated'

3

'D'

'manual'

3

'E'

null

Cases

caseID : int

caseName : string

casePriority : string

1

'Case one'

'low'

2

'Case Two'

'high'

3

'Case Three'

'high'

Foreign Keys

Activities.CaseID

Cases.CaseID

Result

Column1 : int

Column2 : int

1

3

2

2

3

1

See also: