nvl()

The nvl() function evaluates the first argument and returns it if the result is not null. If the result is null, it returns the value of the second argument.

Syntax

Parameters

VAL_EXPR

The expression to evaluate. If the result is not null, that value is returned.

DEFAULT_EXPR

The default value expression to return when VAL_EXPR evaluates to null.

Description

The nvl() function evaluates VAL_EXPR. If the result is not null, it immediately returns that value. If the result is null, it evaluates DEFAULT_EXPR and returns its value.

The return type is determined by the type of the value that is actually returned.

Error codes

N/A

Usage examples

To prepare the WEB_APACHE_SAMPLE table used in these examples, refer to Preparing sample data.

1. Return the referer field value if present, or a default value if absent

   table limit=5 WEB_APACHE_SAMPLE | eval result = nvl(referer, "(none)") | fields referer, result
   

2. Value is null — default value is returned

   json "{'referer': null}" | eval result = nvl(referer, "(none)")
   | # result: (none)
   

3. Field does not exist — default value is returned

   json "{}" | eval result = nvl(referer, "(none)")
   | # result: (none)
   

4. Numeric default value

   json "{'bytes': null}" | eval result = nvl(bytes, 0)
   | # result: 0
   

Compatibility

The nvl() function is available since before Sonar 4.0.