Re: Using the + statement in a Select statement

From: Isaac Blank <izblank_at_yahoo.com>
Date: Tue, 18 Mar 2003 21:31:22 GMT
Message-ID: <KeMda.2284$xS1.230625464@newssvr13.news.prodigy.com>

"Ed Prochak" <edprochak_at_adelphia.net> wrote in message news:3E7780C7.50703_at_adelphia.net...
>
> I forgot the symantics of the ANSI syntax, and it took a bit to find my
> reference book. But given your query, it was easy to write:
>
> Select <something>
> from (select * from A, B where a.f1=b.f1(+) ) BB, C
> where c.f2=bb.f2(+) ;

It is not that important, but the last row should be

where c.f2(+)=bb.f2

> This looks interesting if it happened in real life.

It does happen in real life when you have nullable foreign key columns

>....................................................................But I
do not see the
> "upside down" query that you expected.

One minor thing, you have top re-write <something> , because of the new alias (BB)

> Or did I miss something stupid? 8^>

Yes, you did - my stupidity, actually. :-(. I, for some reason, had in mind a differnet solution:
SELECT <revised something>
FROM A, (SELECT <a subset of something plus b.f1>

            FROM B,C
            WHERE B.f2=C.f2(+) ) BC

 WHERE A.f1=BC.f1(+)

And that is, of course, a perverted way, since the order of joins is opposite to what the original query was about. Received on Tue Mar 18 2003 - 15:31:22 CST