Posted by [IP Address: 199.50.26.35] 'psoft consultant' on August 24, 2001 at 09:34:21 EST:

In Reply to: Re: SQR questions posted by [IP Address: 199.50.26.35] 'Tim Concannon' on August 24, 2001 at 07:11:37 EST:

Thanks for taking the time to explain.

Best Regards

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

Follow Ups:

Post a Followup