"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
