Re: Decode

Hmmm.

Problem is that becomes a nested SQL statement that screws the pooch.

Anyway, the problem was that DECODE cannot handle a comparison in the value portion of the function, like

DECODE (ROUND ( (SYSDATE - TRUNC (my_date)) / 7 + .5, 0) > 13, 13,COUNT(record_id), 0) w13

returns an error.

So, i created an f_over13() function CREATE OR REPLACE FUNCTION F_OVER13
( in_claim_date IN DATE DEFAULT NULL) RETURN NUMBER IS BEGIN IF ROUND (
(SYSDATE - TRUNC (in_claim_date)) / 7 + .5, 0) > 13 THEN RETURN 1; ELSE
RETURN 0; END IF; EXCEPTION WHEN OTHERS THEN raise_application_error
(-20001, SQLERRM); END;
..and called it from within DECODE like this:

DECODE(f_over13(TRUNC(claim_date)), 1,
COUNT(claim_transaction_summary_id), 0) wover13

and it seemed to work by my rigorous testing methods.... :)

--
Posted via dBforums
http://dbforums.com