Re: ANSI Outer Joins

On Mon, 16 Nov 1998 12:49:25 +0100, "Simon Caldwell" <simonATgetrealsystemsDOTcom> wrote:

>Thanks, that appears to work!
>
>Now all I need is a solution for Oracle 7... ;-)
>
>Simon
>

Create a view containing the bit in (...) in the FROM clause. Ie, change

select

    a.col1, x.bc2, x.cc3
from

    a,
    (select b.col1 bc1, b.col2 bc2, c.col1 cc1, c.col3 cc3

     from b, c
     where b.col4 = c.col4) x

    a.col1(+) = x.bc1 and
    a.col2(+) = x.cc1

to

create or replace view x as

    select b.col1 bc1, b.col2 bc2, c.col1 cc1, c.col3 cc3     from b, c
    where b.col4 = c.col4
/

select

    a.col1, x.bc2, x.cc3
from

    a, x
where

    a.col1(+) = x.bc1 and
    a.col2(+) = x.cc1

(obviously, you only need to create the view as a one-off exercise...)

Cheers,
Paul

>Paul Moore wrote in message <364c200b.11218894_at_news.origin-it.com>...
>>On Thu, 12 Nov 1998 17:58:51 +0100, "Simon Caldwell"
>><simonATgetrealsystemsDOTcom> wrote:
>>
>>>
>>>Paul Moore wrote in message <364af9de.21722496_at_news.origin-it.com>...
>>>>I don't understand your question. The syntax you quote works on
>>>>Oracle. If the syntax you are using is different, please post it...
>>>>
>>>No it doesn't. Note that A is outer joined to both B and C. This will
>give
>>>an error
>>>ORA-01416: two tables cannot be outer-joined to each other
>>
>>Ah. Sorry, I missed that point. An'd I'd forgotten that restriction,
>>too.
>>
>>>The SQL-92 syntax would be
>>>select a.col1, b.col2, c.col3
>>>from b
>>> JOIN c ON (b.col4=c.col4)
>>> LEFT OUTER JOIN a ON (a.col1 = b.col1 AND a.col2 = c.col1)
>>>
>>Oh, yes - I know that syntax (or a variant) from Microsoft SQL. I
>>always hated it, as I could never work out what was going on :-)
>>
>>How about (untested) using a select statement in the FROM part - sort
>>of like
>>
>>select
>> a.col1, x.bc2, x.cc3
>>from
>> a,
>> (select b.col1 bc1, b.col2 bc2, c.col1 cc1, c.col3 cc3
>> from b, c
>> where b.col4 = c.col4) x
>>where
>> a.col1(+) = x.bc1 and
>> a.col2(+) = x.cc1
>>
>>
>>I think this sort of thing works, although I haven't tried it
>>myself... It's new in Oracle 8, so not available if you're running
>>Oracle 7.
>>
>>Paul Moore.
>>
Received on Thu Nov 19 1998 - 09:47:36 CST