Re: ARGH - problem with outer join and a view

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'
>
> 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.

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 Morgan Received on Mon May 13 2002 - 11:40:38 CDT