RE: Convert SQL server case statement to Oracle

From: Adams, Matthew (GEA, 088130) <MATT.ADAMS_at_APPL.GE.COM>
Date: Mon, 30 Oct 2000 15:18:33 -0500
Message-Id: <10665.120598@fatcity.com>

What version of Oracle?

Starting with 8i (can't remember if
it's 8.1.5 or 8.1.6), oracle
has a CASE statment of its own.

---

R. Matt Adams - GE Appliances - matt.adams_at_appl.ge.com

      No electrons were harmed in the making of this e-mail.

> -----Original Message-----
> From: Cale, Rick T (Richard) [mailto:RICHARD.T.CALE_at_saic.com]
> Sent: Monday, October 30, 2000 2:59 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Convert SQL server case statement to Oracle
>
>
> Hi All,
>
> I am trying to convert the following case stmt to Oracle but
> am stuck. I
> tried using DECODE but cannot
> handle the <= or <. Equal or Not equal I can convert by using decode.
> Any ideas?
>
> Thanks
> Rick
>
> WHEN EFF_DATE <= V_dStartDate AND END_DATE_ADJ <= V_dEndDate
> THEN COMP_LINK_EMIS_ADJ*DATEDIFF(SS,V_dStartDate
> ,END_DATE_ADJ)/3600*(OP_PCT/100)
>
> WHEN (EFF_DATE BETWEEN V_dStartDate AND V_dEndDate) AND
> END_DATE_ADJ >=
> V_dEndDate
> THEN COMP_LINK_EMIS_ADJ*DATEDIFF
> (SS,EFF_DATE,V_dEndDate)/3600*(OP_PCT/100)
>
> WHEN (EFF_DATE BETWEEN V_dStartDate AND V_dEndDate) AND
> (END_DATE_ADJ
> BETWEEN V_dStartDate AND V_dEndDate)
> THEN
> COMP_LINK_EMIS_ADJ*DATEDIFF(SS,EFF_DATE,END_DATE_ADJ)/3600*(OP
> _PCT/100)
>
> WHEN (EFF_DATE < V_dStartDate) AND (END_DATE_ADJ > V_dEndDate)
> THEN
> COMP_LINK_EMIS_ADJ*DATEDIFF(SS,V_dStartDate,V_dEndDate)/3600*(
> OP_PCT/100)
> ELSE 0
>
>
> Thanks
> Rick
>
>
> Rick Cale, Science Applications International Corp.
> Phone:865-481-2198, fax:865-481-8555
> e-mail: caler_at_saic.com
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Cale, Rick T (Richard)
> INET: RICHARD.T.CALE_at_saic.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Received on Mon Oct 30 2000 - 14:18:33 CST