How to change this DB2 logic to Oracle

[satyakumar]

Hi Guys,

Can any one tell me how to convert this DB2 syntax to Oracle 10g.

CASE
WHEN DAYOFWEEK(reg_d) = 5 THEN (reg_d + 4 days)
WHEN DAYOFWEEK(reg_d) = 6 THEN (reg_d + 4 days)
ELSE (reg_d + 2 days)
END AS "SUPPOSED PKUP ON",

 

[SantaMufasa]

Satya,

Welcome to Tek-Tips and the Oracle forums. May you enjoy your participation here.

There are several ways, in Oracle, to achieve your objective:

select decode(to_char(reg_d,'D')
             ,5,reg_d+4
             ,6,reg_d+4
             ,reg_d+2) "Supposed Pkup on"
      ,to_char(reg_d,'Dy, Mon DD') dt
  from satya
/

Supposed  DT
--------- -----------
05-MAR-90 Sat, Mar 03
12-MAR-90 Thu, Mar 08
19-JUN-91 Mon, Jun 17
09-APR-90 Sat, Apr 07
06-MAR-90 Sun, Mar 04

select case when to_char(reg_d,'D') in (5,6) then reg_d+4
            else reg_d+2
            end "Supposed Pkup on"
      ,to_char(reg_d,'Dy, Mon DD') dt
  from satya
/

Supposed  DT
--------- -----------
05-MAR-90 Sat, Mar 03
12-MAR-90 Thu, Mar 08
19-JUN-91 Mon, Jun 17
09-APR-90 Sat, Apr 07
06-MAR-90 Sun, Mar 04

Let us know which you prefer.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
A fo ben, bid bont.

 

[satyakumar]

Oh Cool, I got it. I used the Second one.

Thank You very Much.