Re: DECODE question

Jim's answer is correct with respect to DECODE. But I would argue that DECODE is the wrong tool for the job.

A far more direct approach is CASE. Where it would be written as:

CASE WHEN (SUM(a.amount) < 10000) THEN 'Yes'

            ELSE 'No'
 END; Certainly seems far less convoluted to me. In 8i you can run this from SQL*Plus or use it in view creation directly but to use it in a procedure it must be presented as native dynamic SQL. In 9i it can be run in procedures without NDS.

Daniel Morgan

Mark Hamilton wrote:

> I'm familiar with how to do a DECODE statement like below to find if the
> first letter of 'j.Field' = 'R':
>
> DECODE(substr(j.Field,1,1), 'R', 'Yes', 'No')
>
> A similar function testing for numeric criteria does not work:
>
> DECODE (sum(a.Amount), < 10000, 'Yes','No').
>
> How can I check for numeric criteria in SQL and give substitutions based on
> the results in a manner similar to DECODE?
>
> Thanks,
>
> Mark Hamilton
Received on Tue Apr 09 2002 - 10:02:39 CDT