Re: complex update

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Tue, 11 Jul 2006 09:20:00 -0400
Message-ID: <WvWdndHZML0fOC7ZnZ2dnUVZ_sWdnZ2d@comcast.com>

<utefan001_at_gmail.com> wrote in message
news:1152617708.868208.35750_at_m79g2000cwm.googlegroups.com...
: Mark C. Stock wrote:
: > "Martin T." <bilbothebagginsbab5_at_freenet.de> wrote in message
: > news:1152600735.068273.198020_at_75g2000cwc.googlegroups.com...
: > : utefan001_at_gmail.com wrote:
: > : > I have an update that is just not working. When I run it on the
latest
: > : > oracle 9i release SQL Plus returns this error.
: > : >
: > : > 01732 "data manipulation operation not legal on this view"
: > : >
: > : > Note that NONE of the tables used below are views. I know I should
: > : > provide more details, but I should also get 3 hours of sleep
tonight.
: > : >
: > : > update (
: > : > select jc.llm as foo, sum(act.cost_amt) bar
: > : > FROM prod.je_sub js,
: > : > ...
: > : >
: > :
: > : You are actually using a view here - an inline view that you are
trying
: > : to update.
: > :
: > : > AND act.task_id = ' 3'
: > : > group by jc.llm)
: > : >
: > :
: > : As far as I know it is not possible to update a View (your inline
view)
: > : that contains a group by clause.
: > :
: > : best,
: > : Martin
: > :
: >
: > Martin's exactly right.
: >
: > However, your set clause makes absolutely no sense -- to set the group
by
: > column to the sum column? Is that what you're actually doing?
: >
: > ++ mcs

:

: I see how the setting the group by column to the sum column makes no
: sense. It won't matter because the group by column is always null when
: this is run. It fact, I will add that requirement to the where clause.
: Anyway, this is not the core problem.

:

: I think the solution will be to use a temporary holding table that
: allows me to work with the results of the "inline view that contains a
: group by clause".

:

: thanks

:

If the group by column is always null, then you don't need a group by, you need a predicate to check for nulls.

Still doesn't make sense. Can you explain what you're trying to accomplish?

++ mcs Received on Tue Jul 11 2006 - 08:20:00 CDT