RE: Converting Inner join in MS-SQL to Oracle SQL syntax
Thanks to Adrian Roe, Jon Walthour, and Rodd Holman for their helpful replies. The text that Jon provided worked perfectly. Scored big points with my users. Thanks for taking the time to help me out, Cherie Machler Oracle DBA Gelco Information Network "Adrian Roe" est.co.uk> cc: Sent by: Subject: RE: Converting Inner join in MS-SQL to Oracle SQL [EMAIL PROTECTED] syntax 08/06/01 04:05 AM Please respond to ORACLE-L Yep, looks good (I'm a SQL7/2000 DBA). "INNER JOIN" etc.. is not SQL Server specific, It's just ANSI standard. If you have. select col1,col2,col3 from table1 inner join table2 on table1.id=table2.id you can then change this to the following to get it to work on Oracle select col1,col2,col3 from table1, table2 where table1.id=table2.id Ade -Original Message- Sent: 03 August 2001 23:28 To: [EMAIL PROTECTED] In short, a Micro$oft T-SQL "inner join" is a PL/SQL regular "join". So (making a few assumptions about the structure of the tables), here is my crack at a rewrite: select KO.KS_KNOWLEDGEOBJECTID from KS_KNOWLEDGEOBJECT KO where KO.KS_AVAILABLE = 0 and not exists (select CS.KS_KNOWLEDGEOBJECTID from KS_CONCEPTSTRING CS , KS_CONCEPT C where CS.KS_CONCEPTID = C.KS_CONCEPTID and KO.KS_KNOWLEDGEOBJECTID = CS.KS_KNOWLEDGEOBJECTID and C.KS_CONCEPTTYPEID = '2'); Jon Walthour - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, August 03, 2001 1:10 PM > A developer has sent me a MS-SQL statement that he recieved from a vendor. > He would like to convert this to run on Oracle. It's pretty close already > but I'm > having trouble with the inner join. > > Is there a kind soul familiar with MS-SQL that could convert this over to > the > right 8.0.4 Oracle syntax? > > select KS_KNOWLEDGEOBJECTID from KS_KNOWLEDGEOBJECT > where KS_AVAILABLE = 0 and not exists > (select KS_KNOWLEDGEOBJECTID from KS_CONCEPTSTRING inner join > KS_CONCEPT > on KS_CONCEPTSTRING.KS_CONCEPTID = KS_CONCEPT.KS_CONCEPTID > where KS_KNOWLEDGEOBJECT.KS_KNOWLEDGEOBJECTID = > KS_CONCEPTSTRING.KS_KNOWLEDGEOBJECTID > and KS_CONCEPTTYPEID = '2') > > > Thanks for any assistance, > > Cherie Machler > Oracle DBA > Gelco Information Network > > > > -- > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jon Walthour 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 Inner join in MS-SQL to Oracle SQL syntax
Cherie, I may be completely wrong on this, but I seem to remember my Intro to SQL instructor at Oracle Ed telling the class that a MS SQL inner join was the same as just a normal join in Oracle. HTH (List please correct me if I'm wrong) Rodd On 03 Aug 2001 09:10:42 -0800, [EMAIL PROTECTED] wrote: > A developer has sent me a MS-SQL statement that he recieved from a vendor. > He would like to convert this to run on Oracle. It's pretty close already > but I'm > having trouble with the inner join. > > Is there a kind soul familiar with MS-SQL that could convert this over to > the > right 8.0.4 Oracle syntax? > > select KS_KNOWLEDGEOBJECTID from KS_KNOWLEDGEOBJECT > where KS_AVAILABLE = 0 and not exists > (select KS_KNOWLEDGEOBJECTID from KS_CONCEPTSTRING inner join > KS_CONCEPT > on KS_CONCEPTSTRING.KS_CONCEPTID = KS_CONCEPT.KS_CONCEPTID > where KS_KNOWLEDGEOBJECT.KS_KNOWLEDGEOBJECTID = > KS_CONCEPTSTRING.KS_KNOWLEDGEOBJECTID > and KS_CONCEPTTYPEID = '2') > > > Thanks for any assistance, > > Cherie Machler > Oracle DBA > Gelco Information Network > > > > -- > 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rodd Holman 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 Inner join in MS-SQL to Oracle SQL syntax
Yep, looks good (I'm a SQL7/2000 DBA). "INNER JOIN" etc.. is not SQL Server specific, It's just ANSI standard. If you have. select col1,col2,col3 from table1 inner join table2 on table1.id=table2.id you can then change this to the following to get it to work on Oracle select col1,col2,col3 from table1, table2 where table1.id=table2.id Ade -Original Message- Sent: 03 August 2001 23:28 To: [EMAIL PROTECTED] In short, a Micro$oft T-SQL "inner join" is a PL/SQL regular "join". So (making a few assumptions about the structure of the tables), here is my crack at a rewrite: select KO.KS_KNOWLEDGEOBJECTID from KS_KNOWLEDGEOBJECT KO where KO.KS_AVAILABLE = 0 and not exists (select CS.KS_KNOWLEDGEOBJECTID from KS_CONCEPTSTRING CS , KS_CONCEPT C where CS.KS_CONCEPTID = C.KS_CONCEPTID and KO.KS_KNOWLEDGEOBJECTID = CS.KS_KNOWLEDGEOBJECTID and C.KS_CONCEPTTYPEID = '2'); Jon Walthour - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, August 03, 2001 1:10 PM > A developer has sent me a MS-SQL statement that he recieved from a vendor. > He would like to convert this to run on Oracle. It's pretty close already > but I'm > having trouble with the inner join. > > Is there a kind soul familiar with MS-SQL that could convert this over to > the > right 8.0.4 Oracle syntax? > > select KS_KNOWLEDGEOBJECTID from KS_KNOWLEDGEOBJECT > where KS_AVAILABLE = 0 and not exists > (select KS_KNOWLEDGEOBJECTID from KS_CONCEPTSTRING inner join > KS_CONCEPT > on KS_CONCEPTSTRING.KS_CONCEPTID = KS_CONCEPT.KS_CONCEPTID > where KS_KNOWLEDGEOBJECT.KS_KNOWLEDGEOBJECTID = > KS_CONCEPTSTRING.KS_KNOWLEDGEOBJECTID > and KS_CONCEPTTYPEID = '2') > > > Thanks for any assistance, > > Cherie Machler > Oracle DBA > Gelco Information Network > > > > -- > 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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jon Walthour 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). -- Live Life in Broadband www.telewest.co.uk The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Statements and opinions expressed in this e-mail may not represent those of the company. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender immediately and delete the material from any computer. == -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Adrian Roe 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[2]: Converting Inner join in MS-SQL to Oracle SQL syntax
Monday, August 06, 2001, 5:05:35 AM, you wrote: AR> Yep, looks good (I'm a SQL7/2000 DBA). AR> "INNER JOIN" etc.. is not SQL Server specific, It's just ANSI standard. It's also supported in Oracle9i. Best regards, Jonathan Gennick mailto:[EMAIL PROTECTED] * 906.387.1698 http://Gennick.com * http://MichiganWaterfalls.com * http://MetalDrums.org -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Gennick 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).
