Spark SQL Conditional Function

This document introduces the syntax of conditional functions in Spark SQL.

You are advised to use the IF function in New Calculation Column of FineDatalink.

For details about the example, see Adding a Column Using the IF Function.

Syntax: NVL(Expression, Default value)

Returns Default value if the value of Expression is null or the value of Expression otherwise.

Example:

In the source table, Ship Name contains null values, as shown in the following figure.

Use Spark SQL. If the Ship Name value is null, return Unknown. Otherwise, return the existing value, as shown in the following figure.

select $[DB Table Input].`Order ID` ,nvl($[DB Table Input].`Ship Name`,'Unknown') from $[DB Table Input]

The result is shown in the following figure.

COALESCE (Expression 1, Expression 2, ...): Returns the first non-null value, or null if all the values are null.

Example:

In the source table, both Ship City and Ship Region have null values, as shown in the following figure.

Use the COALESCE function in Spark SQL to return the first non-null value, as shown in the following figure.

select $[File Input].`Ship City`, $[File Input].`Ship Region`,coalesce($[File Input].`Ship City` ,$[File Input].`Ship Region` ) from $[File Input]

The result is shown in the following figure.

You are advised to use the SWITCH function in New Calculation Column of FineDataLink.

For details about the example, see Adding a Column Using the SWITCH Function.

In FineDataLink, you can use Data Filtering to process the null values.

isnull(Expression): Returns true if the value of Expression is null. Otherwise, return false.

Operations in Data Filtering are shown in the following figure.

is not null(Expression): Return true if the value of Expression is not NULL, otherwise, return false.

Operations in Data Filtering are shown in the following figure.