Null value locator (nvl)

[ 1 Description ] [ 2 How does it work in the search window? ] [ 3 How does it work in LINQ? ]

Description

Adds a new column that returns the value specified as the second argument if the first one is null.

How does it work in the search window?

Select Create column in the search window toolbar, then select the Null value locator operation. This operation needs two arguments:

Argument	Data type

Argument	Data type
When not null ^mandatory	Any
else ^mandatory	Same as When not null

The data type of the new column depends on the result of the operation.

Example

In the demo.ecommerce.data table, we want to create a column that labels the reputation of the IP addresses in the clientIpAddress using the IP Reputation Score (reputationscore) operation, and then assign a specified value in a new column for null occurrences.

Step 1: Label IP addresses using IP Reputation Score

First, create a new column using the IP Reputation Score operation adding clientIpAddress as the argument. Call the new column reputation. We will get some null results when the IP addresses are not found in any reputation list.

Step 2: Assign a specific value to all the null occurrences

Now, create a new column using the Null value locator operation. We want the new column to show 0 when the values in the reputation column are null. Call the new column nvl and add both arguments and enter the following values:

When not null - reputation column
else - Click the pencil icon and enter 0

Click Create column and you will see the following result:

How does it work in LINQ?

Use the operator select... as... and add the operation syntax to create the new column. This is the syntax for the Null value locator operation:

nvl(field_to_check, value_when_null)

Example

You can copy the following LINQ script and try the above example on the demo.ecommerce.data table.

from demo.ecommerce.data
  select reputationscore(clientIpAddress) as reputation,
    nvl(reputation, 0) as nvl