Re: UPDATE question

Hello Gerard,

I believe i've seen in documentation about SELECT statement that it is incorrect to refer to outer scope names from the inline views.

Hope this helps,
Igor

Gerard M. Averill wrote:
>
> Hi all,
>
> I was coding an UPDATE statement the other day and ran into some (what I think
> is) curious behavior by Oracle regarding correlated subqueries. The basic
> form of the UPDATE was :
>
> update T
> set A =
> (
> select ?Expression involving L.X and U.X?
> from
> (
> select max(P.X) X
> from P
> where P.X ?= T.B
> ) L
> , (
> select min(P.X) X
> from P
> where P.X ?= T.B
> ) U
> )
>
> Oracle didn't like the correlated column T.B within the inline views.
> However, when I reworked the statement as follows, it was accepted:
>
> update T
> set A =
> (
> select ?Expression now involving max(L.X) and min(U.X)?
> from P L, P U
> where L.X ?= T.B
> and U.X ?= T.B
> )
>
> My question is: why is T.B an invalid column name within the inline views but
> not in the subquery? It would appear to me that since the subquery is
> correlated, T.B should in either case be regarded as a constant by the
> compiler. Compiler bug? Anyone have any opinions on this?
>
> Regards.
> Gerard
>
> ----
> Gerard M. Averill, Associate Researcher
> CHSRA, University of Wisconsin - Madison
> GAverill_at_chsra.wisc.edu
 

-- 
Igor Sereda,
ITC, Russia