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
FROM TABLE_A

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

5 rows

Hope this helps!

Clark