General Functions

A general function performs operations on data around NULL value handling and comparisons.

General functions include the following:

• NVL '(' expr1, expr2 ')': Returns expr2 if expr1 is null, otherwise returns expr1.

  Inputs: Two expressions, where expr1 can be null.

  Example: NVL(PROMOTIONS.PROMO_COST, 0)

• NVL2 '(' expr1, expr2, expr3 ')': Returns expr2 if expr1 is not null, otherwise returns expr3.

  Inputs: Three expressions.

  Example: NVL2(PROMOTIONS.PROMO_NAME, 'Eligible', 'Not Eligible')

• DECODE '(' value_returned_expression, search, result [, search, result ] ... [, default] ')': Compares an expression to a list of search values and returns the corresponding result or a default value.
  Inputs:

  value_returned_expression ::= expression
  search ::= expression //An expression that matches the type of value_returned_expression
  result ::= expression //An expression that shares a least common type with default_result and the other result expressions
  default_result ::= expressionn //An optional expression that shares a least common type with result

  Example: DECODE(PROMOTIONS.PROMO_NAME, ' NO PROMOTION #', 1, ' NO PROMOTION', 2, 0)

• COALESCE '(' value_returned_expression_list ')': Returns the first non-null value in the list of expressions, otherwise, returns null.

  Inputs: Two or more expressions.

  Example: COALESCE(PROMOTIONS.PROMO_CATEGORY, PROMOTIONS.PROMO_SUBCATEGORY, 'Unknown')

• NULLIF '(' value_returned_expression, value_returned_expression ')': Returns null if expr1 equals expr2; otherwise, returns expr1.

  Inputs: Two expressions for comparison.

  Example: NULLIF(PROMOTIONS.PROMO_CATEGORY, PROMOTIONS.PROMO_SUBCATEGORY)

• GREATEST '(' value_returned_expression_list ')': Returns the largest value among the provided arguments.

  Inputs: Two or more comparable expressions.

  Example: GREATEST(1,4,6,7,null)

• LEAST '(' value_returned_expression_list ')': Returns the smallest value among the provided arguments.

  Inputs: Two or more comparable expressions.

  Example: LEAST(1,4,6,7,null)

• IFNULL '(' expression, expression ')': Returns col2 if col1 is null, or col1 otherwise.

  Inputs: Two expressions for comparison.

  Example: IFNULL(PROMOTIONS.PROMO_CATEGORY, PROMOTIONS.PROMO_SUBCATEGORY)