Re: Outer join query problem

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 11 Aug 2006 20:11:02 -0700
Message-ID: <1155352262.530138.207750@b28g2000cwb.googlegroups.com>

Harnek.Manj_at_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. Received on Fri Aug 11 2006 - 22:11:02 CDT