Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

GREATEST

Applies to: CELONIS 4.7

Description

The GREATEST function returns the greatest element that is not NULL from a set of values.

It supports all PQL types (including table columns) as input. All arguments must be of the same data type. The data type of the result is the same as the data type of the input columns or values.

The GREATEST function requires at least two expressions.

Unicode support

For the STRING type, GREATEST only supports the ASCII range of characters, and not the full Unicode character set.

SYNTAX
GREATEST ( table.column1 , ... , table.columnN )
NULL handling

If all values are NULL, then GREATEST will also return NULL.

Tips

  • In the argument list, you can mix columns with constants. This allows you to define a constant default value for the case.

  • The GREATEST function might be a good alternative to CASE WHEN statements for its reduced syntax.

  • The LEAST function is the reverse function of GREATEST.

Examples

[1] GREATEST with four integer columns as arguments with some null values at different positions.

Query

Column1

GREATEST ( "Table1"."Column1" , "Table1"."Column2" , "Table1"."Column3" , "Table1"."Column4" )

Input

Output

Table1

Column1 : INT

Column2 : INT

Column3 : INT

Column4 : INT

1

2

3

4

5

null

7

8

null

10

11

12

null

null

15

16

17

18

19

20

null

null

null

24

null

null

null

null

29

30

31

32

Result

Column1 : INT

4

8

12

16

20

24

null

32

[2] GREATEST with two columns and a constant as arguments.

Query

Column1

GREATEST ( "Table1"."Column1" , "Table1"."Column2" , 7 )

Input

Output

Table1

Column1 : INT

Column2 : INT

null

1

2

3

null

null

4

5

6

null

Result

Column1 : INT

7

7

7

7

7

[3] GREATEST with four integer columns as arguments containing only null values.

Query

Column1

GREATEST ( "Table1"."Column1" , "Table1"."Column2" , "Table1"."Column3" , "Table1"."Column4" )GREATEST ( "Table1"."Column1" , "Table1"."Column2" , 7 )

Input

Output

Table1

Column1 : INT

Column2 : INT

Column3 : INT

Column4 : INT

null

null

null

null

null

null

null

null

null

null

null

null

Result

Column1 : INT

null

null

null

[4] GREATEST with four float columns as arguments with some null values at different positions.

Query

Column1

GREATEST ( "Table1"."Column1" , "Table1"."Column2" , "Table1"."Column3" , "Table1"."Column4" )

Input

Output

Table1

Column1 : FLOAT

Column2 : FLOAT

Column3 : FLOAT

Column4 : FLOAT

1.0

2.0

3.0

4.0

5.0

null

7.0

8.0

null

10.0

11.0

12.0

null

null

15.0

16.0

17.0

18.0

19.0

20.0

null

null

null

24.0

null

null

null

null

29.0

30.0

31.0

32.0

Result

Column1 : FLOAT

4.0

8.0

12.0

16.0

20.0

24.0

null

32.0

[5] GREATEST with four string columns as arguments with some null values at different positions.

Query

Column1

GREATEST ( "Table1"."Column1" , "Table1"."Column2" , "Table1"."Column3" , "Table1"."Column4" )

Input

Output

Table1

Column1 : STRING

Column2 : STRING

Column3 : STRING

Column4 : STRING

'Text1'

'Text2'

'Text3'

'Text4'

'Text5'

null

'Text7'

'Text8'

null

'Text10'

'Text11'

'Text12'

null

null

'Text15'

'Text16'

'Text17'

'Text18'

'Text19'

'Text20'

null

null

null

'Text24'

null

null

null

null

'Text29'

'Text30'

'Text31'

'Text32'

Result

Column1 : STRING

'Text4'

'Text8'

'Text12'

'Text16'

'Text20'

'Text24'

null

'Text32'

[6] GREATEST with four date columns as arguments with some null values at different positions.

Query

Column1

GREATEST ( "Table1"."Column1" , "Table1"."Column2" , "Table1"."Column3" , "Table1"."Column4" )

Input

Output

Table1

Column1 : DATE

Column2 : DATE

Column3 : DATE

Column4 : DATE

Thu Apr 02 2020 13:01:00.000

Fri Apr 10 2020 08:01:00.000

Sat Mar 28 2020 01:49:00.000

Sun Apr 05 2020 13:44:00.000

null

Sun Mar 01 2020 04:15:00.000

Tue May 12 2020 09:59:00.000

Mon Apr 27 2020 13:01:00.000

null

Sat Feb 01 2020 13:01:00.000

null

Sat Feb 01 2020 13:04:00.000

null

null

Tue Feb 04 2020 13:01:00.000

Sat Feb 01 2020 13:01:00.000

Sun Mar 01 2020 13:01:00.000

null

Wed Mar 11 2020 14:47:00.000

Wed Mar 11 2020 14:48:00.000

null

null

null

Sun Apr 12 2020 13:01:00.000

null

null

null

null

Tue Mar 03 2020 13:10:00.000

Wed Mar 04 2020 09:01:00.000

Sat Feb 01 2020 03:10:00.000

Thu Apr 09 2020 12:10:00.000

Result

Column1 : DATE

Fri Apr 10 2020 08:01:00.000

Tue May 12 2020 09:59:00.000

Sat Feb 01 2020 13:04:00.000

Tue Feb 04 2020 13:01:00.000

Wed Mar 11 2020 14:48:00.000

Sun Apr 12 2020 13:01:00.000

null

Thu Apr 09 2020 12:10:00.000

See also: