Re: This Update doesn't work ... Why?

A query in a FROM clause is not a subquery, but an inline view (look for this term in the documentation). In an inline view you cannot reference things outside the view, just like you can't in a regular view. Why the PL/SQL compiler did not spot this illegal reference I don't know.

Jaap.

On Fri, 12 Apr 2002 14:08:54 +0200, Lío <jgonzalezsuareNO_at_SPAMnexo.es> wrote:

>I've lost the original query because I took a workaround. So now it's
>only curiosity.
>The problem is with an update which takes the values from a query,
>which has a subquery inside.
>The updated table columns are not visible from the subquery's where.
>
>I'm trying to explain it as best as I can. They are visible from the
>query (normal) but when the scope is one more query deeper, they are
>not visible.
>
>On Wed, 10 Apr 2002 15:08:00 GMT, Daniel Morgan
><damorgan_at_exesolutions.com> wrote:
>
>>It would be very helpful if you had included things like the version and
>>the text with ORA-904. But just looking quickly at your statement ... I
>>am wondering what the point of "J" is?
>>
>>Daniel Morgan
>>
>>
>>
>>"Lío" wrote:
>>
>>> I've done an update with this structure (I'll try to make it simple):
>>>
>>> Update T1 set (a, b, c) =
>>> Select ... from
>>> (Select x, y, complex_func(z) "J" from T2 where T2.r = T1.r)
>>> alias_t2,
>>> T3
>>> Where T3.... = alias_t2.J and T3.... = T1.... etc, etc.
>>> )
>>> Where T1....
>>>
>>> This is inside a packaged procedure. It compiles ok, but when I
>>> execute, I get a ORA-904 about the T1.r column.
>>>
>>> Why is it not visible T1 from the T2 subquery?
>>>
>>> Thanks.
>>> --
>>> Julio César Glez Suarez
>>> Xixón, Asturies, Spain
>>> jgonzalezsuareNO_at_SPAMnexo.es (Remove NO SPAM)
>
>Un saludo.
>Julio César Glez Suarez
>jgonzalezsuare_at_nexo.es
Received on Fri Apr 12 2002 - 16:40:58 CDT