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:
Table: SuppDetailsSUPPID SUPPNAME SUPPADDRESS------ -------- -----------1 ABC1 #1, St. Thomas St..2 ABC2 New Brigade, Raj St..3 ABC3
In this situation, your query is also fetching SuppAddress, the value for the SuppAddress where SuppID = 3 is null:
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 is null.
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;
Try this:
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.