UPDATE question

From: Gerard M. Averill <gaverill_at_chsra.wisc.edu>
Date: 1998/05/08
Message-ID: <6ive8v$tcu$1@news.doit.wisc.edu>#1/1

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 Received on Fri May 08 1998 - 00:00:00 CDT