RE: Convert SQL server case statement to Oracle

From: Jeffery Stevenson <jeff_at_mpv.com>
Date: Tue, 31 Oct 2000 09:56:08 -0600
Message-Id: <10666.120676@fatcity.com>

Just remember that decode will short-circuit the evaluations (it will kick out with the first match it comes across)...now take a look at the LEAST and GREATEST functions to see if they give you any ideas on how they might be used in combination with DECODE. :)

Jeffery Stevenson
Chief Database Geek
Medical Present Value, Inc.
Austin, TX

-----Original Message-----
From: Cale, Rick T (Richard) [mailto:RICHARD.T.CALE_at_saic.com] Sent: Monday, October 30, 2000 1: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 Tue Oct 31 2000 - 09:56:08 CST