Re: Decode Question

"Moosie" <mooseneck_at_my-deja.com> wrote in message news:8hmgj0$lmh$1_at_nnrp1.deja.com...
> Sorry for another question, but I'm not a PL/SQL programmer at all, but
> have to produce reports in it (WebDB) for an emergency assignment at my
> job. My question is that I'm looking to sum the number of entry
> accounts made by each operator with dates less than 30 days from now.
> (The account creation date is appended to the account number which is a
> string; that's why I'm extracting part of a string and converting it to
> a date.) If an account is open more than 30 days, I want to count them
> per the operator who put them in the system. I'm also wanting to count
> the type of account it is since there are two different account choices.
> With counting the old entries, I don't care what type of account it is,
> I just want to know if it's old.
>
> This is what I have:
>
> select distinct dwacct_op,
> sum(decode(to_date(substr(dwacct_id, 6, 7)),to_date(substr(dwacct_id, 6,
> 7)) < sysdate - 30, 1,0)), sum(decode(dwacct_goodtype, 'good', 1,0)),
> sum(decode(dwacct_goodtype, 'better', 1,0))
> from dwacct
> group by dwacct_op
>
>
> I'm tearing my hair out about this. I don't want to create two separate
> reports where I have the sum of the number of entries using decode and
> having the <sysdate - 30 in the where cause. I would like all this info
> on one report.
>
> Thank you!!
> Moosie

DECODE can't handle a < or > but you can fake it.

Example, if greater than 12, return "More than a dozen" else return "Not enough!"

Won't work:

select DECODE(to_char(&somenumber),>12,'More than a dozen','Not enough!') from dual;

Will work:

select
DECODE(SIGN(12-&somenumber),-1,'More than a dozen','Not enough!') from dual;

(The SIGN function returns -1, 0, or 1 for negative, zero, or positive numbers so use that to "fake" it)

Use that kind of approach in your example.

-Matt Received on Wed Jun 07 2000 - 00:00:00 CEST