Oracle's DECODE and NVL

"Ross J. Reedstrom" wrote:
> What do DECODE and NVL do?

TABLE_A
COLROW COLVALUE
-----  --------
ROW1   a
ROW2   b
ROW3                <- NULL
ROW4   d
ROW5                <- NULL

5 rows


-- NVL function:
--
-- This function takes a value of any time, and checks
-- to see if the value IS NULL.  If argument is not null, 
-- then it simply returns it's argument.  Otherwise, it
-- returns what is provided as the second argument.
--

SELECT COLROW, NVL(COLVALUE,'XX') AS NOT_NULL_COLVALUE  FROM TABLE_A

COLROW NOT_NULL_COLVALUE
-----  --------
ROW1   a
ROW2   b
ROW3   XX
ROW4   d
ROW5   XX

5 rows

val,lookup,val,default

-- DECODE function  ( CASE/SWITCH like function )
--
-- This function takes an even number of arguments, N
--
-- The function compaires the first argument against each
-- even numbered argument in the argumet list.  If it is
-- a match, then it returns the following value in the
-- argument list.  If there is no match, then the last
-- argument (the default value) is returned.  For matching
-- purposes a NULL = NULL.   The first argument and the
-- middle even arguments must all be the same type, as well
-- as the last argument and the middle odd arguments.
--

SELECT COLROW, DECODE(COLVAL,        'd',4,        'e',0,        NULL,9,        1               ) AS DECODE_COLVALUE
FROMTABLE_A
 

COLROW DECODE_COLVALUE
-----  --------
ROW1   1
ROW2   1
ROW3   9
ROW4   4
ROW5   9

5 rows


Hope this helps!

Clark