RE: converting case in sqlserver to oracle
I believe Oracle 9i supports the CASE syntax. Otherwise, you're probably looking at using some sort of DECODE conbination. Or, I've found a lot of SQL Server queries are overly complex because SQL Server didn't support user-defined functions. I was able to simplify a lot of queries when moving them to Oracle by defining a few functions to replace parts of the query. Dave -Original Message- From: Harvinder Singh [mailto:[EMAIL PROTECTED]] Sent: Tuesday, September 04, 2001 12:31 PM To: Multiple recipients of list ORACLE-L Subject: converting case in sqlserver to oracle Hi, I have a procedure in sql server containg following case statement...(part of select clause) how to convert this statement is oracle format: select au.id, viewID = case when au.id_pi_instance is NULL then id_view else (select viewID = case when pi_props.n_kind = 15 AND child_kind.nm_productview = ed.nm_enum_data then -(au.id_pi_instance + 0x4000) else -au.id_pi_instance end) end,au.instance, Thanks -Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
converting case in sqlserver to oracle
Hi, I have a procedure in sql server containg following case statement...(part of select clause) how to convert this statement is oracle format: select au.id, viewID = case when au.id_pi_instance is NULL then id_view else (select viewID = case when pi_props.n_kind = 15 AND child_kind.nm_productview = ed.nm_enum_data then -(au.id_pi_instance + 0x4000) else -au.id_pi_instance end) end,au.instance, Thanks -Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: converting case in sqlserver to oracle
It's not pretty, but something like this should work: select au.id, decode(au.id_pi_instance,null,id_view, decode(pi_props.n_kind,15, decode(child_kind.nm_productview,ed.nm_enum_data,au.id_pi_instance + 0x4000, au.id_pi_instance), au.id_pi_instance), au.id_pi_instance), au.instance, ... At 09:30 AM 9/4/01 -0800, you wrote: Hi, I have a procedure in sql server containg following case statement...(part of select clause) how to convert this statement is oracle format: select au.id, viewID = case when au.id_pi_instance is NULL then id_view else (select viewID = case when pi_props.n_kind = 15 AND child_kind.nm_productview = ed.nm_enum_data then -(au.id_pi_instance + 0x4000) else -au.id_pi_instance end) end,au.instance, Thanks -Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).