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_Id | ProductName | UnitPrice | UnitsInStock | UnitsOnOrder |
---|---|---|---|---|
1 | Jarlsberg | 10.45 | 16 | 15 |
2 | Mascarpone | 32.56 | 23 | |
3 | Gorgonzola | 15.67 | 9 | 20 |
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