Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2008
    Posts
    7

    Unanswered: How to convert Oracle Decode statement in DB2

    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')

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Read the manual on the CASE statement:

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


    Andy

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Using CASE expressions (not CASE statements) would be the standard way of doing things, i.e. it is more portable.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •