if, case and coalesce

Function: IF

• Functionality

  This function will return the second argument, if the first argument is true, or the third argument, if the first argument is false.

• Arguments

  if(Boolean aCondition, Object aTrueConditonResult,Object aFalseConditonResult)

Note: The 'If' function is not well suited for situations when the condition is null (it simply also returns null). The problem with the interpretation of null is solved by a similar 'CASE' function.

Function: CASE

The function evaluates a list of conditions and returns one of multiple possible result expressions. The CASE expression has two formats:

• The simple CASE expression compares an expression to a set of simple expressions to determine the result.

• The searched CASE expression evaluates a set of Boolean expressions to determine the result.

Both formats support an optional ELSE argument.

Simple CASE expression:

In simple CASE the first argument is input_expression (non-boolean). Then there are pairs of WHEN, THEN expressions. The last (even) argument can be specified as ELSE expression.

case( input_expression,

when_expression, result_expression [ ...n ]

[ , else_result_expression ]

)

Example:

case(

person.lastname,

'Bronson', 'foo',

'Razo', 'spam',

'Quirk', 'and',

null(), 'bar' ,

'eggs'

)

Searched CASE expression:

In search CASE the arguments are pairs of WHEN (boolean), THEN (any) expressions. The last (odd) argument can be specified as ELSE expression. If some WHEN argument is not boolean it is considered as false.

The search CASE is recognized by presence of first boolean argument. If the first argument is not boolean, the simple CASE is used.

case(

Boolean_expression, result_expression [, ...n ]

[ , else_result_expression ]

)

Example 1:

case(

gt_(totalprice, 1000), 'Price > 1000',

gt_(totalprice, 2000), 'Price > 2000',

isNull(totalprice), 'Price is empty',

'Price is OK'

)

Null is returned if ELSE expression is missing and no condition is fulfilled.

Example 2:

case(

gt_(totalprice, 1000), 'Price > 1000',

gt_(totalprice, 2000), 'Price > 2000',

isNull(totalprice), 'Price is empty'

)

Example 3:

case(

not(isNull(netprice)), (netprice*1.21)

)

Function: COALESCE

The COALESCE function accepts a list of parameters, returning the first non-Null value from the list. If no argument is non-Null, then it returns null.

Example:

coalesce(totalprice, 0.0)*1.21

coalesce(person.email, manager.email, king.email, 'god@heaven')