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:
from attachment a,
and a.videoid =1
and s.attachmentid =* a.attachmentid
and s.serverid =* v.serverid
from attachment a,AttaccmentServer s,videoserver v
where s.attachmentid = a.attachmentid
and s.serverid *= v.serverid
and a.videoid *= v.videoid
from attachment a
and a.attachmentid not in (select s.attachmentid from AttaccmentServer s)
and a.videoid not in (select v.videoid from videoserver v)
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: