Re: Using the + statement in a Select statement

From: Ed Prochak <edprochak_at_adelphia.net>
Date: Fri, 21 Mar 2003 17:39:59 GMT
Message-ID: <3E7B523C.6020502@adelphia.net>

Isaac Blank wrote:
> "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

okay then I did miss something. Thanks for pointing that out.

>>This looks interesting if it happened in real life.

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

Yes, now I see that. (I'll need to practice my ANSI syntax more often.)

>
>

>>....................................................................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(+)

Okay so I should have named my alias AB?

>
> And that is, of course, a perverted way, since the order of joins is
> opposite to what the original query was about.
>
>

Translating between even related languages like old style SQL and new style SQL (or american english to british english) can sometimes lead to unusual expressions.

Thanks for the lesson!

-- 
Ed Prochak
running: http://www.faqs.org/faqs/running-faq/
family:  http://web.magicinterface.com/~collins
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost

Received on Fri Mar 21 2003 - 11:39:59 CST