Friday, August 9, 2024

SQL NULL functions

NVL() function

NVL() function is used to provide a default value when the specified field is null. we can also use the COALESCE() function as alternative.

Syntax:

NVL(column_name, default_value)

For example

In the below table, unitOnOrder is optional and can be null.

P_IdProductNameUnitPriceUnitsInStockUnitsOnOrder
1Jarlsberg10.451615
2Mascarpone32.5623 
3Gorgonzola15.67920

SELECT ProductName, UnitPrice * (UnitsInStock + UnitsOnOrder)
FROM Products;

In the example above, if any of the "UnitsOnOrder" values are NULL, the result will be NULL. To avoide such nullibility issue, we use NVL() as below:

SELECT ProductName, UnitPrice * (UnitsInStock + NVL(UnitsOnOrder, 0))
FROM Products;


No comments:

Post a Comment

Featured Post

11g to 12c OSB projects migration points

1. Export 11g OSB code and import in 12c Jdeveloper. Steps to import OSB project in Jdeveloper:   File⇾Import⇾Service Bus Resources⇾ Se...