Skip to main content

PQL47 (PQL Function Library - CPM 4.7)

LEAST

Applies to: CELONIS 4.7

Description

The LEAST function returns the least 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 LEAST function requires at least two expressions.

Unicode support

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

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

If all values are NULL, then LEAST 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 LEAST function might be a good alternative to CASE WHEN statements for its reduced syntax.

  • The GREATEST function is the reverse function of LEAST.

Examples

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

Query

Column1

LEAST ( "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

1

5

10

15

17

24

null

29

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

Query

Column1

LEAST ( "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

1

2

7

4

6

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

Query

Column1

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

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] LEAST with four float columns as arguments with some null values at different positions.

Query

Column1

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

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

Query

Column1

LEAST ( "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

1.0

5.0

10.0

15.0

17.0

24.0

null

29.0

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

Query

Column1

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

Input

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

'Text1'

'Text5'

'Text10'

'Text15'

'Text17'

'Text24'

null

'Text29'

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

Query

Column1

LEAST ( "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

Sat Mar 28 2020 01:49:00.000

Sun Mar 01 2020 04:15:00.000

Sat Feb 01 2020 13:01:00.000

Sat Feb 01 2020 13:01:00.000

Sun Mar 01 2020 13:01:00.000

Sun Apr 12 2020 13:01:00.000

null

Sat Feb 01 2020 03:10:00.000

See also: