Re: Decode

How about something like:

SELECT DECODE (GREATEST(CEIL ( (SYSDATE - TRUNC (my_date)) / 7), 13), 13,COUNT(record_id), 0) w13 .....

to get around the comparision mechanism of DECODE and still avoid the context switch from SQL to PL/SQL?

HTH Dieter

"markag" <member_at_dbforums.com> schrieb im Newsbeitrag news:3cab29fb$3_at_usenetgateway.com...
> 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
Received on Wed Apr 03 2002 - 14:53:41 CST