Login | Register   
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL
Expertise: Intermediate
Dec 12, 2005

Handling Null Values in Oracle

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: SuppDetails

SUPPID	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.

MS Sridhar
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap