Good point. It was the math minor in me. :-)
Jim
"Daniel Morgan" <damorgan_at_exesolutions.com> wrote in message
news:3CB3028C.8278A19F_at_exesolutions.com...
> 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 - 22:22:47 CDT
