Re: How to convert Access queries into Oracle

Besides select, access can do
delete from tab_a inner join tab_b on a.a = b.a where b.b=blah;

In Oracle this has to be a correlated subquery: delete from tab_a ta
where exists(
select 'x' from tab_b tb
where ta.a = tb.a
and tb.b=blah
);

Can someone show an alternative?

"Brian Tkatch" <SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK> wrote in message news:3a27b4df.913833515_at_news.alt.net...
> On 30 Nov 2000 10:12:19 +0100, ivanovic27_at_icqmail.com wrote:
>
> >Hello everybody
> >Can somebody help me??
> >I have to convert some Access queries that use Left Join and Right Join
 into Oracle and I don't know how to do it.
> >
>
> Just went through this myself, I think I understand, so here goes:
>
> LEFT JOIN and RIGHT JOIN are merely synonyms for an OUTER JOIN.
>
> In Access the following statements are equal, or so I believe.
>
> SELECT Moo.Id FROM Cow LEFT JOIN Moo ON Cow.Moo = Moo.Id;
> SELECT Moo.Id FROM Cow LEFT OUTER JOIN Moo ON Cow.Moo = Moo.Id;
>
> The word LEFT or RIGHT merely specifies which table has the columns
> that are *not* referenced in the other table. So, in the above
> example, if table "Cow" has ids that are not in the "Moo" table, a
> NULL will show up as the result for that row (Moo.Id). This is because
> it is a LEFT join, and the table on the left of the join statement is
> "Cow". So LEFT or RIGHT specifies the table that has the extra rows.
>
> An INNER JOIN, in Access can be specified as
>
> SELECT Moo.Id FROM Cow INNER JOIN Moo ON Cow.Moo = Moo.Id;
>
> - or -
>
> SELECT Moo.Id FROM Cow, Moo WHERE Cow.Moo = Moo.Id;
>
> These will render identical results.
>
> Now Oracle can handle INNER JOINs in the latter method
>
> SELECT Moo.Id FROM Cow, Moo WHERE Cow.Moo = Moo.Id;
>
> To make it an OUTER Join you add the (+) symbol to the column of the
> table that is being referenced, that is the one that *must* have the
> values, or return a null. This is the exact *opposite* of Access.
>
> SELECT Moo.Id FROM Cow, Moo WHERE Cow.Moo = Moo.Id(+);
>
> In Access this is a LEFT JOIN. For a RIGHT JOIN:
>
> SELECT Moo.Id FROM Cow, Moo WHERE Cow.Moo(+) = Moo.Id;
>
> If I'm wrong, please tell me, I just dived into outer joins, for both
> Access and Oracle, yesterday...
>
> Brian
>
Received on Tue Dec 05 2000 - 16:22:54 CST