Please use the form below to provide your feedback. Because your feedback is valuable to us, the information you submit in this form is recorded in our issue tracking system (JIRA), which is publicly available. You can track the status of your feedback using the ticket number displayed in the dialog once you submit the form.

A newer version of this documentation is available.

View Latest

Conditional Functions for Unknowns

  • reference
    +

    Conditional functions evaluate expressions to determine if the values and formulas meet the specified condition.

    COALESCE(expression1, expression2, ...)

    Alias for IFMISSINGORNULL().

    DECODE(expression, search1, result1 [, search2, result2, ...] [, default])

    Arguments

    expression

    [Required] Any valid expression.

    search1, search2, ...

    [At least 1 is required] Any values.

    result1, result2, ...

    [At least 1 is required] Any values.

    default

    [Optional] Any value.

    The function requires a minimum of three arguments. The first argument is the expression. This is followed by one or more pairs of search and result arguments. If there is an even number of arguments, the last argument is the default argument. If there is an odd number of arguments, the default is not specified.

    Return Value

    Returns the result corresponding to the first search that matches the expression. If none of the search values match the expression, the function returns the value of default, or returns NULL if default is not specified.

    Example

    To try the examples in this section, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

    Find the UTC offset of all airports in the United States whose altitude is greater than 1000:

    SELECT a.airportname AS Airport,
DECODE(a.tz, "Pacific/Honolulu", "-10:00",
             "America/Anchorage", "-09:00",
             "America/Los_Angeles", "-08:00",
             "America/Denver", "-07:00",
             "America/Chicago", "-06:00",
             "America/New_York", "-05:00", a.tz) AS UTCOffset
FROM airport a
WHERE a.country = "United States" AND a.geo.alt > 1000
LIMIT 5;
    Results
    [
  {
    "Airport": "Indian Mountain Lrrs",
    "UTCOffset": "-09:00"
  },
  {
    "Airport": "Sparrevohn Lrrs",
    "UTCOffset": "-09:00"
  },
  {
    "Airport": "Bicycle Lake Aaf",
    "UTCOffset": "-08:00"
  },
  {
    "Airport": "Twentynine Palms Eaf",
    "UTCOffset": "-0:800"
  },
  {
    "Airport": "Grants Milan Muni",
    "UTCOffset": "-07:00"
  }
]

    IFMISSING(expression1, expression2, ...)

    Arguments

    expression1, expression2, ...

    [At least 2 are required] Any valid expressions.

    Return Value

    Returns the first non-MISSING value. Returns NULL if all values are MISSING.

    Example

    SELECT IFMISSING(null, missing, "abc", 123) AS Mix, (1)
       IFMISSING(null, null, null) AS AllNull, (2)
       IFMISSING(missing, missing, missing) AS AllMissing;
    Results
    [
  {
    "AllMissing": null,
    "AllNull": null, (2)
    "Mix": null (1)
  }
]
    1 The first non-MISSING value is NULL, so this function returns NULL.
    2 The first non-MISSING value is NULL, so this function returns NULL.

    IFMISSINGORNULL(expression1, expression2, ...)

    This function has an alias COALESCE().

    Arguments

    expression1, expression2, ...

    [At least 2 are required] Any valid expressions.

    Return Value

    Returns first non-NULL, non-MISSING value. Returns NULL if all values are MISSING or NULL.

    Example

    SELECT IFMISSINGORNULL(null, missing, "abc", 123) AS Mix,
       IFMISSINGORNULL(null, null, null) AS AllNull,
       IFMISSINGORNULL(missing, missing, missing) AS AllMissing;
    Results
    [
  {
    "AllMissing": null,
    "AllNull": null,
    "Mix": "abc"
  }
]

    IFNULL(expression1, expression2, ...)

    Arguments

    expression1, expression2, ...

    [At least 2 are required] Any valid expressions.

    Return Value

    Returns first non-NULL value. Returns NULL if all values are NULL.

    Example

    SELECT IFNULL(null, missing, "abc", 123) AS Mix, (1)
       IFNULL(null, null, null) AS AllNull,
       IFNULL(missing, missing, missing) AS AllMissing; (2)
    Results
    [
  {
    "AllNull": null
  }
]
    1 The first non-NULL value is MISSING, so this function returns MISSING.
    2 The first non-NULL value is MISSING, so this function returns MISSING.

    MISSINGIF(expression1, expression2)

    Arguments

    expression1, expression2, ...

    [Exactly 2 are required] Any valid expressions.

    Return Value

    Returns MISSING if expression1 = expression2, otherwise returns expression1. Returns MISSING if either input is MISSING or if both inputs are MISSING. Returns NULL if either input is NULL or if both inputs are NULL.

    Example

    SELECT MISSINGIF("abc", 123) AS Different,
       MISSINGIF("abc", "abc") AS Same;
    Results
    [
  {
    "Different": "abc"
  }
]

    NULLIF(expression1, expression2)

    Arguments

    expression1, expression2, ...

    [Exactly 2 are required] Any valid expressions.

    Return Value

    Returns NULL if expression1 = expression2, otherwise returns expression1. Returns MISSING if either input is MISSING or if both inputs are MISSING. Returns NULL if either input is NULL or if both inputs are NULL.

    Example

    SELECT NULLIF("abc", 123) AS Different,
       NULLIF("abc", "abc") AS Same;
    Results
    [
  {
    "Different": "abc",
    "Same": null
  }
]

    NVL(expression1, expression2)

    Arguments

    expression1, expression2, ...

    [Exactly 2 are required] Any valid expressions.

    Return Value

    Returns expression1 if expression1 is not MISSING or NULL. Returns expression2 if expression1 is MISSING or NULL.

    Example

    To try the examples in this section, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

    SELECT name as Name, NVL(iata, "n/a") as IATA
FROM airline
LIMIT 5;
    Results
    [
  {
    "IATA": "Q5",
    "Name": "40-Mile Air"
  },
  {
    "IATA": "TQ",
    "Name": "Texas Wings"
  },
  {
    "IATA": "A1",
    "Name": "Atifly"
  },
  {
    "IATA": "n/a",
    "Name": "Jc royal.britannica"
  },
  {
    "IATA": "ZQ",
    "Name": "Locair"
  }
]

    NVL2(expression, value1, value2)

    Arguments

    expression

    [Required] Any valid expression.

    value1, value2, ...

    [Exactly 2 are required] Any values.

    Return Value

    Returns value1 if expression is not MISSING or NULL. Returns value2 if expression is MISSING or NULL.

    Example

    To try the examples in this section, set the query context to the inventory scope in the travel sample dataset. For more information, see Query Context.

    SELECT name as Name, NVL2(directions, "Yes", "No") as DirectionsAvailable
FROM hotel
LIMIT 5;
    Results
    [
  {
    "DirectionsAvailable": "No",
    "Name": "Medway Youth Hostel"
  },
  {
    "DirectionsAvailable": "No",
    "Name": "The Balmoral Guesthouse"
  },
  {
    "DirectionsAvailable": "Yes",
    "Name": "The Robins"
  },
  {
    "DirectionsAvailable": "Yes",
    "Name": "Le Clos Fleuri"
  },
  {
    "DirectionsAvailable": "Yes",
    "Name": "Glasgow Grand Central"
  }
]