Posted by [IP Address: 199.43.32.22] 'Tim Concannon' on August 24, 2001 at 07:11:37 EST:
In Reply to: SQR questions posted by [IP Address: 199.43.32.22] 'psoft con' on August 23, 2001 at 16:25:45 EST:
: Can someone explain how and why 'decode' & 'nvl' are used in SQRs.
: TIA
These are actually ORACLE Functions. NVL is used for NULL Value Substitution. Lets suppose you supply warehouse space for different clients and need to estimate floor space needed for new material coming into the warehouse. But for some of the clients in your database the floor space field has a NULL value. You want to account for space so you want to substitute an average size when you encounter a NULL value.
For Example your database contains the following data
Select CLIENT,FLOOR_SPACE from NEW_INCOMING;
CLIENT FLOOR_SPACE
------------ -----------
Andys Rigs 200x200
Toms Tents 300x300
Salllys Dolls
Sallys Dolls has a NULL value. If you want a estimate a value to ensure some availble space for the new material coming in it can be done using the NVL Function.
Select Client,NVL(FLOOR_SPACE,250x250) from NEW_INCOMING;
CLIENT FLOOR_SPACE
------------ -----------
Andys Rigs 200x200
Toms Tents 300x300
Salllys Dolls 250x250
Anytime the select comes across a NULL value for FLOOR_SPACE the value of 250x250 will appear. The database will not store this value however, it will still be a NULL in the database. Unless, you are doing an update or insert and using the NVL function to put a value into the database if the value of a field is NULL.
DECODE -
The DECODE function will substitute one value for another (or translate a value) through a series of if-else logic.
Lets suppose the Division of Motor Vehicles has a number code for eye color of all licensed drivers. And the number code does not have a translate value table associated with it. Rather than produce a report of drivers and their eye color with codes for eyes you want the color code replaced with the actual color.
Select NAME,EYE_COLOR from DMV_EYE_TABLE;
NAME EYE_COLOR
----- ---------
Bob 1
Jill 2
Frank 3
Yolanda 2
To have the actual color appear you could use the decode function.
Select NAME,
DECODE(EYE_COLOR,'1','Blue','2','Hazel','3','Brown');
NAME EYE_COLOR
----- ---------
Bob Blue
Jill Hazel
Frank Brown
Yolanda Hazel
Decode is similar to an if else
Decode(fieldname,'If this value','Then show this',If this value','then show this' ...)
Decode is very powerful - if you use your imagination you can really flip some data around.
Hope this is helpful,
Tim Concannon