|Subject:||Re: Outer join query problem|
|Posted by:||Charles Hooper (hooperc20…@yahoo.com)|
|Date:||11 Aug 2006|
> Hey Everybody,
> I am trying to run the following query on oracle 10g & I endup with an
> 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 <>
> 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.
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):
R2.DATEFLD <= '11-AUGUST-2006'
CALCTYPE <> 'LINEOFCREDIT') ST
AND NVL(S2.RATE,0) = 0
AND S2.DATEMATURE > '11-AUGUST-2006'
AND S2.DATESETTLE <='11-AUGUST-2006'
AND UPPER(S2.STATUS) = 'DONE';
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
PC Support Specialist
K&M Machine-Fabricating, Inc.
Outer join query problem posted by Harnek.Ma…@gmail.com on 11 Aug 2006