Oftentimes, a query will return null values for some of the columns in a query. However, to display "null" as the value in the generated report isn't a very effective way to display this information.
Oracle provides an easy solution for this problem.
For example, suppose you are generating a report for a supplier. The table schema may look like this:
SUPPID SUPPNAME SUPPADDRESS
------ -------- -----------
1 ABC1 #1, St. Thomas St..
2 ABC2 New Brigade, Raj St..
In this situation, your query is also fetching SuppAddress
, the value for the SuppAddress
where SuppID = 3
Select SUPPID, SUPPNAME, SUPPADDRESS from SuppDetails;
To avoid displaying null, try using this query:
Select SUPPID, SUPPNAME, NVL(SUPPADDRESS, SUPPNAME) from SuppDetails;
This returns the SUPPNAME
if the SUPPADDRESS
Here's another example: suppose you've got a table with a COMMISSION column in it and there's a possibility that the column is null. Instead of using this:
Select COMMISSION from TABLE_COMM would return null;
Select NVL(COMMISSION,0) from TABLE_COMM would return 0(Zero).
Using NVL in your queries where you expect null to be returned will also help avoid processing on the front-end code as well.