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@telew To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
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).
------------------------------------------------------------------------------
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).
--
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).
