Re: Outer join query problem

Giganews Newsgroups
Subject: Re: Outer join query problem
Posted by:  Charles Hooper (hooperc20…@yahoo.com)
Date: 11 Aug 2006

Harnek.Ma…@gmail.com wrote:
> Hey Everybody,
>
> I am trying to run the following query on oracle 10g & I endup with an
> error
>
> ORA-01799: a column may not be outer-joined to a subquery
>
> select s2.portfolio, parvalue pv, nvl(r.rate,0) rate from std s2
> join rates r on s2.fltindex = r.indicator
> and r.datefld = (select max(datefld) from rates r2 where r2.datefld <=
> '11-August-2006' and r2.indicator = s2.fltindex)
> where s2.type in (select type from stdtype where calctype <>
> 'LineOfCredit')
> and nvl(s2.rate,0) = 0
> and s2.datemature > '11-August-2006'
> and s2.datesettle <='11-August-2006'
> and upper(s2.status) = 'DONE'
>
> but this same query works fine on Oracle 9i.
>
> Is there any body who can tell me that what's problem can be. The issue
> is that I can change the query but it should be compatible to SQL
> Server also.
>
> Thanks
> Harnek

It is quite possibly caused by Oracle automatically rewriting the SQL
statement before being processed.  The query optimizers in the various
versions of Oracle try different approaches to improve performance.
The SQL statement may be rewritten something like this, converting the
subqueries into inline views (not verified):
SELECT
  S2.PORTFOLIO,
  PARVALUE PV,
  NVL(R.RATE,0) RATE
FROM
  STD S2
  RATES R,
  (SELECT
    R2.INDICATOR,
    MAX(DATEFLD) MAX_DATEFLD
  FROM
    RATES R2
  WHERE
    R2.DATEFLD <= '11-AUGUST-2006'
  GROUP BY
    R2.INDICATOR) MD,
  (SELECT DISTINCT
    TYPE
  FROM
    STDTYPE
  WHERE
    CALCTYPE <> 'LINEOFCREDIT') ST
WHERE
  MD.INDICATOR=S2.FLTINDEX
  AND MD.MAX_DATEFLD=R.DATEFLD
  AND S2.FLTINDEX=R.INDICATOR
  AND S2.TYPE=ST.TYPE
  AND NVL(S2.RATE,0) = 0
  AND S2.DATEMATURE > '11-AUGUST-2006'
  AND S2.DATESETTLE <='11-AUGUST-2006'
  AND UPPER(S2.STATUS) = 'DONE';

Note the:
  MD.INDICATOR=S2.FLTINDEX
  AND MD.MAX_DATEFLD=R.DATEFLD
where Oracle is being asked to join the two columns returned by the MD
inline view to two different tables.  This may be the code logic that
is causing the "ORA-01799: a column may not be outer-joined to a
subquery".  You may not receive this error if you use the above SQL
statement.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

Replies

None

In response to

Outer join query problem posted by Harnek.Ma…@gmail.com on 11 Aug 2006