PREVIOUS Logic group

NEXT Web group

Flow group

1. Conditional

Description

Conditional operation that will return the second argument if the expression given as first argument is true, and the third one in other case.

Format

ifthenelse(arg_1, arg_2, arg_3) → result

  • arg_1 : Expression to evaluate
  • arg_2 : The result expected if arg_1 is true
  • arg_3 : The result expected if arg_1 is false
  Observations
  • arg_1 and arg_i must be compatible.
  • arg_i and arg_i+1 are used as a pair (original, transform to), so at least one pair of them must be included.
  • arg_i and arg_n must be compatible.
  • If arg_n is not specified, null will be returned.

Examples

  Example 1

ifthenelse("a" = "b", "yes", "no")

    • arg_1 : "a" = "b"
    • arg_2 : "yes"
    • arg_3 : "no"
    • result : no
  Example 2

ifthenelse(10 < 20, 10, 20);

    • arg_1 : 10 < 20
    • arg_2 : 10
    • arg_3 : 20
    • result : 10
  Example 3

ifthenelse(col -> "at", upper(col), "(" + col + ")")
wherecol is a column with the following elements: "dog", "cat", "bird", "bat", "cat", "dog"

    • arg_1 : col -> "at"
    • arg_2 : upper(col)
    • arg_3 : "(" + col + ")"
    • result : Depends on the value of each element of col
      • element in uppercase → if the element contains at
      • element between parentheses → otherwise
      So, there would be a new column with the following elements: "dog", "CAT", "(bird)", "BAT", "CAT", "(dog)"

The result of evaluating arg_1 must be a boolean. arg_2 and arg_3 must have a compatible type

2. Decode

Description

Conditional operation that maps from one set of values into another by returning a specific argument with either another specific argument or a default value (null if not set), depending on the result of an equality/match condition. This operation is another conditional control flow statement, but unlike the if-then-elseoperation, multiple execution paths are allowed.

Format

decode(arg_1, arg_i, arg_i+1, [arg_n]) → result

  • arg_1 : The argument to evaluate/compare
  • arg_i : The argument(s) that can match arg_1
  • arg_i+1 : The result expected if arg_imatches arg_1
  • arg_n : The default value if no match is found (optional)

All the elements allow any supported type.

  Observations
  • arg_1 and arg_i must be compatible.
  • arg_i and arg_i+1 are used as a pair (original, transform to), so at least one pair of them must be included.
  • arg_i and arg_n must be compatible.
  • If arg_n is not specified, null will be returned.

Examples

  Example 1

 decode(1 = 2, true, "OK", false, "DIFF")

    • arg_1 : 1 = 2
    • arg_2 : true
    • arg_3 : "OK"
    • arg_4 : false
    • arg_5 : "DIFF"
    • result: DIFF

Note that this example has no default value.

  Example 2

decode(col, 1, "dog", 2, "cat", 3, "bird", "none") - where col is a column of integers containing the following elements: 0, 1, 0, 3, 4, 2.

    • arg_1 : col
    • arg_2 : 1
    • arg_3 : "dog"
    • arg_4 : 2
    • arg_5 : "cat"
    • arg_6 : 3
    • arg_7 : "bird"
    • arg_8 : "none"
    • result : Depends on the value of each element of col
      • none → if elem = 0
      • dog → if elem = 1
      • cat → if elem = 2
      • bird → if elem = 3

The result is a new column with the following elements: none, dog, none, bird, none, cat, where all the original values have been mapped to the new specified ones, and the rest to the default value, none.

3. Null value locator

Description

Conditional operation that returns the value specified as second argument if the first one is null.

Format

nvl(arg_1, arg_2) → result

  • arg_1 : Argument to check
  • arg_2 : The result expected if arg_1 is null

Examples

  Example 1

nvl(col, "BLANK")
where col is a column with the following elements: "cat", "dog", null, "bird", null, null.

    • arg_1 : col
    • arg_2 : "BLANK"
    • result : Depends on the value of each element of col
      • "BLANK" → if elem is null
      • original element → otherwise
      • "cat", "dog", "BLANK", "bird", "BLANK", "BLANK".

Both arguments must have supported types.

Have we answered your question?

If not, please contact our technical support team via email by clicking the button below.

CONTACT US

PREVIOUS Logic group

NEXT Web group