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 field 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 field 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 field depends on the result of the operation.

Example

In the demo.ecommerce.data table, we want to create a field 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 field for null occurrences.

Step 1: Label IP addresses using IP Reputation Score

First, create a new field using the IP Reputation Score operation adding clientIpAddress as the argument. Call the new field 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 field using the Null value locator operation. We want the new field to show 0 when the values in the reputation field are null. Call the new field nvl and add both arguments and enter the following values:

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

Click Create field 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 field. 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