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
March 9, 2025
+ 12

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.

n1ql
Copy
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`.inventory.airport a
WHERE a.country = "United States" AND a.geo.alt > 1000
LIMIT 5;
Results
json
Copy
[
  {
    "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

n1ql
Copy
SELECT IFMISSING(null, missing, "abc", 123) AS Mix, (1)
       IFMISSING(null, null, null) AS AllNull, (2)
       IFMISSING(missing, missing, missing) AS AllMissing;
Results
json
Copy
[
  {
    "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

n1ql
Copy
SELECT IFMISSINGORNULL(null, missing, "abc", 123) AS Mix,
       IFMISSINGORNULL(null, null, null) AS AllNull,
       IFMISSINGORNULL(missing, missing, missing) AS AllMissing;
Results
json
Copy
[
  {
    "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

n1ql
Copy
SELECT IFNULL(null, missing, "abc", 123) AS Mix, (1)
       IFNULL(null, null, null) AS AllNull,
       IFNULL(missing, missing, missing) AS AllMissing; (2)
Results
json
Copy
[
  {
    "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

n1ql
Copy
SELECT MISSINGIF("abc", 123) AS Different,
       MISSINGIF("abc", "abc") AS Same;
Results
json
Copy
[
  {
    "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

n1ql
Copy
SELECT NULLIF("abc", 123) AS Different,
       NULLIF("abc", "abc") AS Same;
Results
json
Copy
[
  {
    "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

n1ql
Copy
SELECT name as Name, NVL(iata, "n/a") as IATA
FROM `travel-sample`.inventory.airline
LIMIT 5;
Results
json
Copy
[
  {
    "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

n1ql
Copy
SELECT name as Name, NVL2(directions, "Yes", "No") as DirectionsAvailable
FROM `travel-sample`.inventory.hotel
LIMIT 5;
Results
json
Copy
[
  {
    "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"
  }
]