Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: ARGH - problem with outer join and a view

Re: ARGH - problem with outer join and a view

From: Lothar Armbruester <lothar.armbruester_at_t-online.de>
Date: Tue, 14 May 2002 18:32:03 +0200
Message-ID: <PM0003A0F3CDFE8830@hades.none.local>


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.de
Received on Tue May 14 2002 - 11:32:03 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US