Re: Outer join query problem

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.

Looking at it the first thing that pops out is:

r2.datefld <= > '11-August-2006'

If datefld is a DATE field then it is not a string: Use TO_DATE and don't rely on implicit conversion. But, quite frankly I have a hard time believing Oracle knows what to do with the word 'August'.

How about turning this:
(select max(datefld) from rates r2 where r2.datefld <= '11-August-2006' and r2.indicator = s2.fltindex)

Into this:
(SELECT MAX(datefld)
  FROM rates r, std s
  WHERE r.datefld <= TO_DATE('11-AUG-2006', 'DD-MON-YYYY')   AND r.indicator = s.fltindex)

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org