Hi,

I created a decode statement in Oracle to find out the Total Weekly count. If I run on Monday it should give me the count of prior week group by each weekday. When run on Tue it should give me the count on Monday of current week etc.

Please let me know how to write the below statement in DB2 version 8

select
decode( to_char( to_date('06/02/2008','mm/dd/yyyy'),'DAY'), 'MONDAY ', to_date('06/02/2008','mm/dd/yyyy') -7,
decode( to_char( to_date('06/02/2008','mm/dd/yyyy'),'DAY'), 'TUESDAY ', to_date('06/02/2008','mm/dd/yyyy') -1,
decode( to_char( to_date('06/02/2008','mm/dd/yyyy'),'DAY'), 'WEDNESDAY', to_date('06/02/2008','mm/dd/yyyy') -2,
decode( to_char( to_date('06/02/2008','mm/dd/yyyy'),'DAY'), 'THURSDAY ', to_date('06/02/2008','mm/dd/yyyy') -3,
decode( to_char( to_date('06/02/2008','mm/dd/yyyy'),'DAY'), 'FRIDAY ', to_date('06/02/2008','mm/dd/yyyy') -4,
decode( to_char( to_date('06/02/2008','mm/dd/yyyy'),'DAY'), 'SATERDAY ', to_date('06/02/2008','mm/dd/yyyy') -5,
decode( to_char( to_date('06/02/2008','mm/dd/yyyy'),'DAY'), 'SUNDAY ', to_date('06/02/2008','mm/dd/yyyy') -6, to_date('09/04/2099','mm/dd/yyyy')
))))))) as today,
sum( table.calls) as total_calls
from
table
where
table.vdate >=
decode( to_char( to_date('06/02/2008','mm/dd/yyyy'),'DAY'), 'MONDAY ', to_date('06/02/2008','mm/dd/yyyy') -7,
decode( to_char( to_date('06/02/2008','mm/dd/yyyy'),'DAY'), 'TUESDAY ', to_date('06/02/2008','mm/dd/yyyy') -1,
decode( to_char( to_date('06/02/2008','mm/dd/yyyy'),'DAY'), 'WEDNESDAY', to_date('06/02/2008','mm/dd/yyyy') -2,
decode( to_char( to_date('06/02/2008','mm/dd/yyyy'),'DAY'), 'THURSDAY ', to_date('06/02/2008','mm/dd/yyyy') -3,
decode( to_char( to_date('06/02/2008','mm/dd/yyyy'),'DAY'), 'FRIDAY ', to_date('06/02/2008','mm/dd/yyyy') -4,
decode( to_char( to_date('06/02/2008','mm/dd/yyyy'),'DAY'), 'SATERDAY ', to_date('06/02/2008','mm/dd/yyyy') -5,
decode( to_char( to_date('06/02/2008','mm/dd/yyyy'),'DAY'), 'SUNDAY ', to_date('06/02/2008','mm/dd/yyyy') -6, to_date('09/04/2099','mm/dd/yyyy')
)))))))
and
table.vdate < to_date('06/02/2008','mm/dd/yyyy')

Read the manual on the CASE statement:

http://publib.boulder.ibm.com/infoce...n/r0005646.htm


Andy

Using CASE expressions (not CASE statements) would be the standard way of doing things, i.e. it is more portable.