Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> 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" <Adrian.Roe_at_telew To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> est.co.uk> cc: Sent by: Subject: RE: Converting Inner join in MS-SQL to Oracle SQL root_at_fatcity.com 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: ORACLE-L_at_fatcity.com
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" <ORACLE-L_at_fatcity.com>
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.
>
>
> >
>
> > >
>> To REMOVE yourself from this mailing list, send an E-Mail message
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jon Walthour INET: jonw_at_fuse.net 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). ------------------------------------------------------------------------------ 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: Adrian.Roe_at_telewest.co.uk 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Cherie_Machler_at_gelco.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 Wed Aug 08 2001 - 12:53:50 CDT