Re:Convert SQL server case statement to Oracle

From: <dgoulet_at_vicr.com>
Date: Tue, 31 Oct 2000 08:05:28 -0500
Message-Id: <10666.120661@fatcity.com>

Looks like a need for PL/SQl as in

if( EFF_DATE <= V_dStartDate  AND END_DATE_ADJ <= V_dEndDate) then 
   COMP_LINK_EMIS_ADJ*DATEDIFF

(SS,EFF_DATE,V_dEndDate)/3600*(OP_PCT/100)
elsif(EFF_DATE BETWEEN V_dStartDate AND V_dEndDate) AND END_DATE_ADJ >= V_dEndDate) then THEN COMP_LINK_EMIS_ADJ*DATEDIFF
(SS,EFF_DATE,V_dEndDate)/3600*(OP_PCT/100)
else <something>
endif;

Dick Goulet

____________________Reply Separator____________________

Subject: Convert SQL server case statement to Oracle Author: "Cale; Rick T (Richard)" <RICHARD.T.CALE_at_saic.com> Date: 10/30/00 11:58 AM

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 Tue Oct 31 2000 - 07:05:28 CST