RE: Convert SQL server case statement to Oracle

From: krao <krao_at_callidussoftware.com>
Date: Mon, 30 Oct 2000 13:26:40 -0800
Message-Id: <10665.120605@fatcity.com>

I think it's possible to handle <, =, > etc. using decode.

For example, to implement:

	If a<b then v1:= 'StrictlyLess';
	elseif a=b then v1 := 'EqualTo';
	else v1 := 'StrictlyGreater';

do:
v1 := decode( trunc(a/b), 0, 'StrictlyLess', decode((trunc(a/b)* round(a/b) , 1, 'EqualTo', 'StrictlyGreater' );

hth,
- Rao
-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Cale, Rick T (Richard)
Sent: Monday, October 30, 2000 11:59 AM
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 - 15:26:40 CST