You are viewing the documentation for a prerelease version.

View Latest

Conditional Functions for Unknowns

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

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 `travel-sample` 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

SELECT a.name as Name, NVL(a.iata, "n/a") as IATA
FROM `travel-sample` a
WHERE a.type="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

SELECT h.name as Name, NVL2(h.directions, "Yes", "No") as DirectionsAvailable
FROM `travel-sample` h
WHERE h.type="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"
  }
]