Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: ARGH - problem with outer join and a view
Daniel Morgan wrote:
> Dwayne King wrote:
>
>> Hello all,
>>
>> I have a query defined as:
>>
>> select CO.CONTENT_OBJECT_SID, CO.OBJECT_TYPE_CD,
>> NVL(OLT.OBJECT_NM, CO.OBJECT_NM) AS OBJECT_NM,
>> NVL(OLT.OBJECT_DESC,
>> CO.OBJECT_DESC) AS OBJECT_DESC, CO.PARENT_OBJECT_SID,
>> OLT.LANGUAGE_CD AS LANGUAGE_CD
>> from CONTENT_OBJECT CO, OBJECT_LANGUAGE_TEXT OLT
>> where CO.CONTENT_OBJECT_SID = OLT.CONTENT_OBJECT_SID(+)
>> and OLT.DELETED_DT is NULL
>> AND co.CONTENT_OBJECT_SID=1001000 AND olt.language_cd(+) ='DD'
>>
[...]
>>
>> What I am looking for is that a column on the first table
>> (CONTENT_OBJECT) is used if there is no entry in the second table
>> (OBJECT_LANGUAGE_TEXT), which is what happens when run outside a
> view.
>
> The reason it can't see the second outer join is that it is not an
> outer
> join: It is a syntax error.
>
> The correct syntax is:
>
> where CO.CONTENT_OBJECT_SID = OLT.CONTENT_OBJECT_SID(+)
> and OLT.DELETED_DT is NULL
> and CO.CONTENT_OBJECT_SID=1001000
> and OLT.LANGUAGE_CD = 'DD'
>
> The outer-join, or inner-join is a join condition between tables or
> views.
> This is just a qualification statement for which rows in OLT are
> filtered
> in.
>
Daniel,
here you are not right. The comparison with the literal 'DD' kills the
outer join. Consider the following:
SQL> select * from test1;
COL1 COL2
---------- ----------
1 1 2 2
SQL> select * from test2;
COL2 CO
---------- --
1 DD
SQL> select * from test1,test2
2 where test1.col2=test2.col2(+);
COL1 COL2 COL2 CO
---------- ---------- ---------- --
1 1 1 DD 2 2
1 select * from test1,test2
2 where test1.col2=test2.col2(+)
3* and test2.col3='DD'
SQL> /
COL1 COL2 COL2 CO
---------- ---------- ---------- --
1 1 1 DD
1 select * from test1,test2
2 where test1.col2=test2.col2(+)
3* and test2.col3(+)='DD'
SQL> /
COL1 COL2 COL2 CO
---------- ---------- ---------- --
1 1 1 DD 2 2
If you don't do an "outer join with the constant", the second row is
omited, so Dwayne's problem must be somewhere else.
I don't see where, though. :-)
Maybe the view uses another optimizer mode than the plain SQL...
Hope that helps
-- Lothar Armbrüster | la_at_oktagramm.de Hauptstr. 26 | la_at_heptagramm.de D-65346 Eltville | lothar.armbruester_at_t-online.deReceived on Tue May 14 2002 - 11:32:03 CDT