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.

Conditional Functions

  • Capella Analytics
  • reference
This topic describes the builtin SQL++ conditional functions for Capella 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.