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 -> ARGH - problem with outer join and a view

ARGH - problem with outer join and a view

From: Dwayne King <dwayne_at_kridan-consulting.com>
Date: 13 May 2002 08:52:53 -0700
Message-ID: <97abf669.0205130752.14ec0ad8@posting.google.com>


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'

This works great until I put it in a view. I defined the view as everything
until BEFORE "AND co.CONTENT_OBJECT_SID=..." When I do this, the query's
behaviour changes. It's almost like it doesn't see the second outer join
specifier (+). Can you explain?

In what order are the clauses evaluated? i.e. is the WHERE clause evaluated
before or after the outer join? The WHERE appears to happen after the outer
join when run as a regular query, but before the join when part of the query is
in a view.

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.
Received on Mon May 13 2002 - 10:52:53 CDT

Original text of this message

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