Re: Decode Question

Decode only handles exact matches, not less-than/greater-than. Try this:

select dwacct_op, sum(1),
sum( decode( dwacct_goodtype, 'good', 1, 0)), sum( decode( dwacct_goodtype, 'better', 1, 0)) from dwacct
where sysdate > to_date( substr( dwacct_id,6,7)) + 30 group by dwacct_op
union
select dwacct_op, 0,
sum( decode( dwacct_goodtype, 'good', 1, 0)), sum( decode( dwacct_goodtype, 'better', 1, 0)) from dwacct
where sysdate <= to_date( substr( dwacct_id,6,7)) + 30 group by dwacct_op
/

You may also need to specify the format of the date string:

where sysdate <= to_date( substr( dwacct_id,6,7), 'MONDDYY') + 30 -- for example

"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
>
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Jun 08 2000 - 00:00:00 CEST