Re: Question: SQL outer join

Thank you very much for your help. I changed the where clause with b.period (+)= '01' and the SQL works for my intended purpose this time. The outer joins were indeed ignored when I set: b.period = '01'.

Thanks again

Donna Hormozian wrote in message <01be12b4$2b9a5360$abe0ba89_at_kurosh-home>...
>It is possible to apply the outer join on multiple rows.
>The query is fine except for the following:
> and b.period = '01'
>should instead read
> and b.period (+)= '01'
>
>If a filter condition is applied to the table being outer joined to, the
>outer joins are ignored unless the outer join operator is also added to the
>filter condition.
>
>Kurosh Hormozian
>Visioneering Partners Inc.
>
>Sheldon Wang <sheldon.wang_at_cdott.com> wrote in article
><72sgml$chj$1_at_demon.uunet.ca>...
>> Hi,
>>
>> I have a question about using the Outer join in SQL statement. I know I
>can
>> use the outer join on a single row in a table. However is it possible to
>> apply the Outer join on multiple rows in one table? The following is my
>> example:
>>
>> // outer joins with multiple rows (composite key). Is the following ok?
>> // trans and detail are two tables.
>>
>> select
>> a.cont_no,
>> a.supertype_code,
>> a.type_code,
>> a.subtype_code,
>> a.trans_no,
>> a.region_code,
>> b.value
>>
>> From
>> trans a,
>> detail b
>>
>> where
>> a.cont_no= 'W8476-1-EN01/01NJ'
>> and b.period = '01'
>> and a.cont_no = b.cont_no(+)
>> and a.supertype_code = b.supertype_code(+)
>> and a.type_code = b.type_code(+)
>> and a.subtype_code = b.subtype_code(+)
>> and a.trans_no = b.irb_trans_no(+)
>> and a.region_code = b.region_code(+)
>>
>> order by
>> a.cont_no,
>> a.region_code,
>> a.trans_no,
>> a.supertype_code,
>> a.type_code,
>> a.subtype_code
>>
>> // end of SQL
>>
>> I would appreciate your advice very much.
>>
>> Sheldon
>>
>>
>>
Received on Thu Nov 19 1998 - 09:58:34 CST