Control Flow Functions

These functions can be used to vary the outcome of queries, dependent on context, such as the values of the current row. This is often used to convert values from technical IDs to readable values, without the need to create a lookup table that needs joining. In Okera, these functions are used to replace clear-text values with obfuscated ones, like tokenized or masked values based on the original data and the permissions of the user executing the query.

Function	Description
CASE	Case operator
decode()	Special type of Case operator
if()	Fixed IF ... ELSE construct
ifnull()	Same as if(), but more terse
nullif()	Returns NULL when the given arguments are equal

The following explains the functions and statements in more detail.

• CASE

  Compares the result of an expression with one or more cases and, if one equals, returns the matching result. Syntax can be one of the following two:

  CASE <reference> WHEN [<search>] THEN <result>
  [WHEN [<search>] THEN <result>]
  [ELSE <result>] END
  

  or

  CASE WHEN [<condition>] THEN <result>
  [WHEN [<condition>] THEN <result>]
  [ELSE <result>] END
  

  All arguments are expressions, that means they are evaluated and their resulting values is used for the comparisions. For the first CASE form , the first match, that is where the value of <reference> is equal to the value of a given <search> expression, determines the associated <result> value returned. For the second form, each <condition> expression is evaluated and the first one to return a true value (that is, not false, 0 or NULL) is used to return its associated <result> value. Otherwise, it returns the value of the <result> in the ELSE part, if given, or NULL instead.

  Of note is that the first <result> expression result type is used to infer the types for all other results. This means that you cannot switch from, for example, 10 * 10 (which is a SMALLINT) to "foobar" (which is a STRING) for separate <result> expression in the same function call. Conversely though, other expressions can promote the infered type to a convertible one, for example, from SMALLINT to DOUBLE (see last example below). Mixing types that cannot be converted, say from an INT to STRING will raise an error noting the incompatible return types.

  Example: The two ways the CASE operator works

  > SELECT CASE 1 WHEN 1 THEN "one" WHEN 2 THEN "two" ELSE "more" END;
  one
  
  > SELECT CASE WHEN 1>0 THEN "true" ELSE "false" END;
  true
  

• <primitive_type> decode(<expression>, ...)

  The decode() function works similar to the CASE statement, but is a function insteat of a statement. Its syntax is:

  decode(<reference>, <search_1>, <result_1> [,<search_n>, <result_n>][, <default>])
  

  All arguments are expressions, that means they are evaluated and their resulting values is used for the comparisions. The first match, that is where the value of <reference> is equal to the value of a given <search> expression, determines the associated <result> value returned. Otherwise, it returns the value of <default>, if given, or NULL instead.

  Of note is that the first <result> expression result type is used to infer the types for all other results. This means that you cannot switch from, for example, 10 * 10 (which is a SMALLINT) to "foobar" (which is a STRING) for separate <result> expression in the same function call. Conversely though, other expressions can promote the infered type to a convertible one, for example, from SMALLINT to DOUBLE (see last example below). Mixing types that cannot be converted, say from an INT to STRING will raise an error noting the incompatible return types.

  Note: This function is known from Oracle and provided for broader SQL support.

  Example: Using the decode() function to translate values

  > SELECT decode(1, 1, "one", 2, "two", "something else");
  one
  
  > SELECT decode(NULL, 1, "one", 2, "two", null, "not set");
  not set
  

  Example: Using expressions and implict return type inference

  > SELECT decode(2.0 * 1, 1.0 * 2, 10 * 10, 2, 10 / 10, 10 + 10);
  -- Same as SELECT CASE 2.0 * 1 WHEN 1.0 * 2 THEN 10 * 10 WHEN 2 THEN 10 / 10 ELSE 10 + 10 END;
  100
  
  > SELECT typeof(DECODE(2, 2, 10, 2, 10 / 10, 10 + 10));
  DOUBLE
  

• <primitive_type> if(<expression>, ...)

  Okera supports the if() function, with the following syntax: if(<expr1>, <expr2>, <expr3>). Based on the Boolean value of the first expression, the second or third expression is evaluated and its result returned. More specifically, when <expr1> is evluating to true, then <expr2> is evaluated and its result returned. Otherwise, when <expr1> is false, then `<expr3> is evaluated and returned.

  Note: Okera does not support the IF ... THEN ... construct as known from RDBMSs. The IF ... THEN ... is usually used inside stored procedures, which are also not supported by Okera.

  Example: The if() function

  > SELECT if(NULL, 0, 1);
  1
  
  > SELECT if(0 < 1, 0, 1);
  0
  
  > SELECT if(0 > 1, 10 + 10, 20 * 20);
  400
  

• <primitive_type> ifnull(<expression>, <expression>), isnull(), and nvl()

  These functions are all equivalent and require two arguments, for instance: ifnull(<expr1>, <expr2>). Similar to the if() function, but using the first expression to make the decision, it returns the result of the first or second evaluated expression. When the first expression <expr1> is evaluated and its result is considered true, it is returned from the function call. Otherwise, <expr2> is evaluated and its result returned.

  Note: The reason for these synomyms is dialects found in commercial RDBMS systems. For example, Oracle uses nvl() while SQL Server is using isnull(). You can also express the same with the coalesce() function.

  Example: Using the ifnull() function

  > SELECT ifnull(10 * NULL, 20 * 20);
  400
  
  > SELECT ifnull(10 * 10, 20 * 20);
  100
  

• <primitive_type> nullif(<expression>, <expression>)

  The function requires two arguments nullif(<expr1>, <expr2>). It returns NULL if <expr1> = <expr2> is true, otherwise it returns the result from evaluating <expr1>. This is the same as CASE WHEN <expr1> = <expr2> THEN NULL ELSE <expr1> END.

  Example: Using the nullif() function

  > SELECT nullif(10, 10)
  -- Same as: IF(10 IS DISTINCT FROM 10, 10, NULL)
  null
  
  > SELECT nullif(10, 11)
  -- Same as: IF(10 IS DISTINCT FROM 11, 10, NULL)
  10