-
Hi All,
I'd like to convert the following select statement from T-SQL to PL/SQL. When I run the following query in SQL Server, it works great. When I try to run it in Oracle (with Oracle syntax) I get the following error:
"A table may be outer joined to only one other table"
The following is the select statement:
select a.attachmentid,v.serverid,s.AttachmentStatusID
from attachment a,
videoserver v,
AttachmentServer s
where a.videoid=v.videoid
and a.videoid =1
and s.attachmentid =* a.attachmentid
and s.serverid =* v.serverid
union all
select a.attachmentid,v.serverid,s.AttachmentStatusID
from attachment a,AttaccmentServer s,videoserver v
where s.attachmentid = a.attachmentid
and a.videoid=2
and s.serverid *= v.serverid
and a.videoid *= v.videoid
union all
select a.attachmentid,null,null
from attachment a
where a.videoid=2
and a.attachmentid not in (select s.attachmentid from AttaccmentServer s)
and a.videoid not in (select v.videoid from videoserver v)
Any help would greatly be appreciated.
- G
-
To avoid outer join, use UNION. Break your SELECT statement.
-
Yoiks!
Are you sure this is what you want to do? :)
Okay, okay, assuming you know what you're doing, here is how to re-write it in Oracle:
SELECT
___T.ATTACHMENTID_________,
___T.SERVERID____________,
___S.ATTACHMENTSTATUSID
FROM
___(
______SELECT
_________A.ATTACHMENTID______,
_________V.SERVERID____________
______FROM
_________ATTACHMENT_________A,
_________VIDEOSERVER_________V
______WHERE
_________A.VIDEOID _________=___1
_________V.VIDEOID_________=___A.VIDEOID
___)__________________T___ ,
___ATTACHMENTSERVER___S
WHERE
___AND___S.ATTACHMENTID (+)=___T.ATTACHMENTID
___AND___S.SERVERID______(+)=___T.SERVERID
UNION ALL
SELECT
___T.ATTACHMENTID_________,
___V.SERVERID____________,
___T.ATTACHMENTSTATUSID
FROM
___(
______SELECT
_________A.ATTACHMENTID_________,
_________S.ATTACHMENTSTATUSID
______FROM
_________ATTACHMENT_________A,
_________ATTACHMENTSERVER___S
______WHERE
_______________A.VIDEOID_________=___2
_________AND___S.ATTACHMENTID______=___A.ATTACHMENTID
___)__________________T___,
___VIDEOSERVER_________V,
WHERE
___AND___V.SERVERID______(+)=___T.SERVERID___
___AND___V.VIDEOID______(+)=___T.VIDEOID______
UNION ALL
SELECT
___A.ATTACHMENTID_________,
___V.SERVERID____________,
___S.ATTACHMENTSTATUSID
FROM
___ATTACHMENT_________A,
___VIDEOSERVER_________V,
___ATTACHMENTSERVER___S
WHERE
_________A.VIDEOID_________=___2
___AND___S.ATTACHMENTID___(+)=___A.ATTACHMENTID
___AND___S.ATTACHMENTID___IS______NULL
___AND___V.VIDEOID______(+)=___A.VIDEOID
___AND___V.VIDEOID______IS______NULL
There are a few things to note.
- First of all, there is a fundamental difference between how SQLServer and Oracle treat outer-joins. In SQLServer, *all* regular joined predicates are performed first, followed by all outer-joined predicates. In Oracle, all outer-joined (bad term, BTW) non-table predicates are done, then outer-joins, then non-outer-join predicates on outer-joined tables. This sounds odd, but look at the last part of the UNION. Here, Oracle did the outer-join of the tables, *then* allows you to further restrict the set by those where the join produced a NULL. This is much faster than a NOT IN, and probably why they went with this method.
- However, this method of processing invalidates the ability to outer-join to more than 1 table. This is only a minor inconvenicene, as it can be worked around with a sub-select in the FROM clause. *This* functionality did not used to exist in SQLServer, which is probably why they did *not* go with Oracle's methodology. Regardless, look at what I did with the first 2 parts and you'll understand. I turned the 2 tables into 1 - now we are no longer breaking the rule.
One final note, the logic in your third select seems wrong. You are restricting to the set WHERE NOT IN and NOT IN. Therefore, if the record is in one, but not the other, it will still be in the result set. Therefore, if it is in S but not V, it will be included in the result set, *but* you hard-coded a NULL for the V.SERVERID field. You also hard-coded a NULL for the S.ATTACHMENTSTATUSID field, thereby not returning the actually value for records that pass your test. Another advantage of my solution is that I return the actual column. This should be included *whether or not* you want to include those records. If you don't want to include records that exist in *either* table, simply change the last part of the query to:
___AND___S.ATTACHMENTID___(+)=___A.ATTACHMENTID
___AND___V.VIDEOID______(+)=___A.VIDEOID
___AND___(
____________S.ATTACHMENTID___IS______NULL
_________OR___V.VIDEOID______IS______NULL
_________)
Hope this helps. Let me know if you have any questions,
- Chris
BTW, replace _ with space or ___ with a 3-char tab. I only add these to keep formatting intact in this left-justifying forum :)
-
Chrisrlong you were very helpful. I made some minor corrections to the query and everything worked.
Thanks alot!
- Gary
