Conditional Functions

reference

This topic describes the builtin SQL++ conditional functions for Enterprise Analytics.

if_null (ifnull)

Syntax:

if_null(expression1, expression2, ... expressionN)

Finds first argument whose value is not null and returns that value
Arguments:
- expressionI : an expression of any type.
Return Value:
- a null if all arguments evaluate to null or no arguments specified
- a value of the first non-null argument otherwise

Example:

{
    "a": if_null(),
    "b": if_null(null),
    "c": if_null(null, "analytics"),
    "d": is_missing(if_null(missing))
};

The expected result is:

{ "a": null, "b": null, "c": "analytics", "d": true }

The function has an alias ifnull.

if_missing (ifmissing)

Syntax:

if_missing(expression1, expression2, ... expressionN)

Finds first argument whose value is not missing and returns that value
Arguments:
- expressionI : an expression of any type.
Return Value:
- a null if all arguments evaluate to missing or no arguments specified
- a value of the first non-missing argument otherwise

Example:

{
    "a": if_missing(),
    "b": if_missing(missing),
    "c": if_missing(missing, "analytics"),
    "d": if_missing(null, "analytics")
};

The expected result is:

{ "a": null, "b": null, "c": "analytics", "d": null }

The function has an alias ifmissing.

if_missing_or_null (ifmissingornull, coalesce)

Syntax:

if_missing_or_null(expression1, expression2, ... expressionN)

Finds first argument whose value is not null or missing and returns that value.
Arguments:
- expressionI : an expression of any type.
Return Value:
- a null if all arguments evaluate to either null or missing, or no arguments specified
- a value of the first non-null, non-missing argument otherwise

Example:

{
    "a": if_missing_or_null(),
    "b": if_missing_or_null(null, missing),
    "c": if_missing_or_null(null, missing, "analytics")
};

The expected result is:

{ "a": null, "b": null, "c": "analytics" }

The function has two aliases: ifmissingornull and coalesce.

if_inf (ifinf)

Syntax:

if_inf(expression1, expression2, ... expressionN)

Finds first argument which is a non-infinite number (INF or -INF), while skipping missing.
Arguments:
- expressionI : an expression of any type.
Return Value:
- a null if null argument or any other non-number argument was encountered before the first non-infinite number argument
- the first non-infinite number argument encountered. Otherwise, null is returned

Example:

{
    "a": is_null(if_inf(null, null)),
    "b": is_null(if_inf(missing, missing)),
    "c": is_null(if_inf(double("INF"), double("INF"))),
    "d": if_inf(1, null, missing),
    "e": is_null(if_inf(null, missing, 1)),
    "f": is_null(if_inf(missing, null, 1)),
    "g": if_inf(float("INF"), 1),
    "h": to_string(if_inf(float("INF"), double("NaN"), 1))
};

The expected result is:

{ "a": true, "b": true, "c": true, "d": 1, "e": true, "f": true, "g": 1, "h": "NaN" }

The function has an alias ifinf.

if_nan (ifnan)

Syntax:

if_nan(expression1, expression2, ... expressionN)

Finds first argument which is a non-NaN number, while skipping missing
Arguments:
- expressionI : an expression of any type.
Return Value:
- a null if null argument or any other non-number argument was encountered before the first non-NaN number argument
- the first non-NaN number argument encountered. Otherwise, null is returned

Example:

{
    "a": is_null(if_nan(null, null)),
    "b": is_null(if_nan(missing, missing)),
    "c": is_null(if_nan(double("NaN"), double("NaN"))),
    "d": if_nan(1, null, missing),
    "e": is_null(if_nan(null, missing, 1)),
    "f": is_null(if_nan(missing, null, 1)),
    "g": if_nan(float("NaN"), 1),
    "h": to_string(if_nan(float("NaN"), double("INF"), 1))
};

The expected result is:

{ "a": true, "b": true, "c": true, "d": 1, "e": true, "f": true, "g": 1, "h": "INF" }

The function has an alias ifnan.

if_nan_or_inf (ifnanorinf)

Syntax:

if_nan_or_inf(expression1, expression2, ... expressionN)

Finds first argument which is a non-infinite (INF or -INF) and non-NaN number, while skipping missing
Arguments:
- expressionI : an expression of any type.
Return Value:
- a null if null argument or any other non-number argument was encountered before the first non-infinite and non-NaN number argument
- the first non-infinite and non-NaN number argument encountered. Otherwise null is returned

Example:

{
    "a": is_null(if_nan_or_inf(null, null)),
    "b": is_null(if_nan_or_inf(missing, missing)),
    "c": is_null(if_nan_or_inf(double("NaN"), double("INF"))),
    "d": if_nan_or_inf(1, null, missing),
    "e": is_null(if_nan_or_inf(null, missing, 1)),
    "f": is_null(if_nan_or_inf(missing, null, 1)),
    "g": if_nan_or_inf(float("NaN"), float("INF"), 1)
};

The expected result is:

{ "a": true, "b": true, "c": true, "d": 1, "e": true, "f": true, "g": 1 }

The function has an alias ifnanorinf.

null_if (nullif)

Syntax:
```
null_if(expression1, expression2)
```
Compares two arguments and returns null if they are equal, otherwise returns the first argument.
Arguments:
- expressionI : an expression of any type.
Return Value:
- null if argument1 = argument2 evaluates to true
- the first argument if argument1 = argument2 evaluates to false, null, or missing

Example:

{
    "a": null_if("analytics", "analytics"),
    "b": null_if(1, 2),
    "c": null_if(1, "analytics"),
    "d": null_if("analytics", 1),
    "e": null_if(1, null),
    "f": null_if(1, missing),
    "g": null_if(null, 1),
    "h": is_missing(null_if(missing, 1))
};

The expected result is:

{ "a": null, "b": 1, "c": 1, "d": "analytics", "e": 1, "f": 1, "g": null, "h": true }

The function has an alias nullif.

missing_if (missingif)

Syntax:
```
missing_if(expression1, expression2)
```
Compares two arguments and returns missing if they are equal, otherwise returns the first argument.
Arguments:
- expressionI : an expression of any type.
Return Value:
- missing if argument1 = argument2 evaluates to true
- the first argument if argument1 = argument2 evaluates to false, null, or missing

Example:

{
    "a": is_missing(missing_if("analytics", "analytics")),
    "b": missing_if(1, 2),
    "c": missing_if(1, "analytics"),
    "d": missing_if("analytics", 1),
    "e": missing_if(1, null),
    "f": missing_if(1, missing),
    "g": missing_if(null, 1),
    "h": is_missing(missing_if(missing, 1))
};

The expected result is:

{ "a": true, "b": 1, "c": 1, "d": "analytics", "e": 1, "f": 1, "g": null, "h": true }

The function has an alias missingif.

nan_if (nanif)

Syntax:
```
nan_if(expression1, expression2)
```
Compares two arguments and returns NaN value if they are equal, otherwise returns the first argument.
Arguments:
- expressionI : an expression of any type.
Return Value:
- NaN if argument1 = argument2 evaluates to true
- the first argument if argument1 = argument2 evaluates to false, null, or missing

Example:

{
    "a": to_string(nan_if("analytics", "analytics")),
    "b": nan_if(1, 2),
    "c": nan_if(1, "analytics"),
    "d": nan_if("analytics", 1),
    "e": nan_if(1, null),
    "f": nan_if(1, missing),
    "g": nan_if(null, 1),
    "h": is_missing(nan_if(missing, 1))
};

The expected result is:

{ "a": "NaN", "b": 1, "c": 1, "d": "analytics", "e": 1, "f": 1, "g": null, "h": true }

The function has an alias nanif.

posinf_if (posinfif)

Syntax:
```
posinf_if(expression1, expression2)
```
Compares two arguments and returns +INF value if they are equal, otherwise returns the first argument.
Arguments:
- expressionI : an expression of any type.
Return Value:
- +INF value of type double if argument1 = argument2 evaluates to true
- a value of the first argument otherwise (if argument1 = argument2 evaluates to false, null, or missing)

Example:

{
    "a": to_string(posinf_if("analytics", "analytics")),
    "b": posinf_if(1, 2),
    "c": posinf_if(1, "analytics"),
    "d": posinf_if("analytics", 1),
    "e": posinf_if(1, null),
    "f": posinf_if(1, missing),
    "g": posinf_if(null, 1),
    "h": is_missing(posinf_if(missing, 1))
};

The expected result is:

{ "a": "+INF", "b": 1, "c": 1, "d": "analytics", "e": 1, "f": 1, "g": null, "h": true }

The function has an alias posinfif.

neginf_if (neginfif)

Syntax:
```
neginf_if(expression1, expression2)
```
Compares two arguments and returns -INF value if they are equal, otherwise returns the first argument.
Arguments:
- expressionI : an expression of any type.
Return Value:
- -INF value of type double if argument1 = argument2 evaluates to true
- a value of the first argument if argument1 = argument2 evaluates to false, null, or missing

Example:

{
    "a": to_string(neginf_if("analytics", "analytics")),
    "b": neginf_if(1, 2),
    "c": neginf_if(1, "analytics"),
    "d": neginf_if("analytics", 1),
    "e": neginf_if(1, null),
    "f": neginf_if(1, missing),
    "g": neginf_if(null, 1),
    "h": is_missing(neginf_if(missing, 1))
};

The expected result is:

{ "a": "-INF", "b": 1, "c": 1, "d": "analytics", "e": 1, "f": 1, "g": null, "h": true }

The function has an alias neginfif.