Functions

Use functions to access specified routines from SQL. They take one or more arguments and return a result. The following table describes the functions.
Table 1. Functions
Name Description
case Searched form: 
CASE
  WHEN <search-condition-1> THEN <result-1>
  WHEN <search-condition-2> THEN <result-2>
...
  WHEN <search-condition-n> THEN <result-n>
  ELSE <default-result>
END

Search conditions can be arbitrarily complex and results can be expressions.
case Value form: 
CASE <test-value>
  WHEN <comparand-value-1> THEN <result-1>
  WHEN <comparand-value-2> THEN <result-2>
...
  WHEN <comparand-value-n> THEN <result-n>
  ELSE <default-result>
END

Test values, comparand values, and results can be expressions.
cast (<value> as <type>) Is available to convert from one data type to another data type. For more information, see Cast conversions.
coalesce(arg1, arg2, …) Returns its first non-null argument or null if all arguments are null. isnull is a synonym for SQL Server compatibility.
date_part ('field', <datetime value>) Similar to extract, extracts a numeric datetime or time zone field from a datetime or interval value. For example, date_time('day', <datetime-value>). For a list of valid arguments, see Extract date and time values. For syntax, see Table 1.
decode(<expr>, <search1>,<result1>, …<search N>, <result N>, <default>) Compares the <expr> to each search value. If the <expr> is equal to the search, decode returns the result. If there is no match, decode returns the default, or if the default is omitted, returns null.

For more information, see Decode example.
extract (field from <datetime value>) Extracts a numeric datetime or time zone field from a datetime or interval value. For example, extract(year from <datetime-value>). For a list of valid arguments, see Extract date and time values. For syntax, see Table 1.
max(<expression>, <expression>, ...) Returns the maximum value specified in a set of two or more values. The arguments must be compatible and each argument must be an expression that returns a value of any data type. The result is null if any argument is null.
Note: If you specify only one expression, the system uses the max() aggregate function to process the input value. See Aggregate functions for a description of the aggregate function.
min(<expression>, <expression>, ...) Returns the minimum value specified in a set of two or more values. The arguments must be compatible and each argument must be an expression that returns a value of any data type. The result is null if any argument is null.
Note: If you specify only one expression, the system uses the min() aggregate function to process the input value. See Aggregate functions for a description of the aggregate function.
nullif(a,b) Returns a null value if a=b, otherwise it returns a.
nvl(x,y) Returns the first argument if it is not null, otherwise it returns the second argument. For example, nvl(hire_date, current_date) returns the current_date if the hire_date is null.

nvl is equivalent to the SQL coalesce function, and is short hand for the case expression “case when x is not null then x else y end.” For more information, see NVL example.
nvl2(x,y,z) Returns the second argument if the first argument is not null, otherwise it returns the third argument.

nvl2 is short hand for the case expression “case when x is not null then y else z end.” For more information, see NVL2 example.