Null value locator (nvl)
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:
|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.
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
as... and add the operation syntax to create the new column. This is the syntax for the Null value locator operation:
You can copy the following LINQ script and try the above example on the
from demo.ecommerce.data select reputationscore(clientIpAddress) as reputation, nvl(reputation, 0) as nvl