In Oracle/PLSQL, the NVL function allows you to substitute a value when a null value is encountered.

The syntax for the NVL function is:

NVL(a_value, replace_it_with )

a_value is the string to test for a null value.
replace_it_with is the value returned if string1 is null.

Examples,

select NVL(product_descr, product_name) from products;

This statement would return product_name everytime it encounters a null value in the product_descr field. If it finds a value (i.e. any value other than null value) in product_descr field than it will return that value.

select NVL(numeric_field,0) from a_table;

This statement would return 0 everytime it finds a null value in the numeric_field.

You can not use NVL like,

Select NVL(distinct(product_name),’not found’),price, category from a_table;

One possible solution for this is to use distinct before NVL in the statement.

Select distinct NVL(product_name,’not found’),price, category  from a_table;

If you wanted to get distinct values before using NVL function, you can do this.

Select

from ( select distinct product_name, price, category from a_table; )

Also not like, (NVL does not function like DECODE.)

NVL(field1, replace_value1,field2, replace_value2,DEFAULT_FOR_ALL)

You will need to make separate NVL function calls for each column.

select NVL(product_name, ‘not found’), NVL(product_descr, ‘not found’) from a_table;

Hope this helps.

Kirtan