| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: complex update
I would like to vote G Quesnel for the President of
comp.databases.oracle.misc. You rock. It worked.
A couple of you indicated that setting the group by column to the sum column was pure sql blasphemy. I agree. It is gone.
Thanks!
DA Morgan wrote:
> 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,
> > prod.je_det jd,
> > prod.pmxject xref,
> > prod.pmctact act,
> > jobcost4 jc
> > WHERE
> > jc.project_id = act.project_id
> > AND jc.task_id = act.task_id
> > AND jc.fiscal_year = js.year
> > AND jc.period = js.period
> > AND js.je_ccn = '07'
> > AND js.je_ccn = jd.je_ccn
> > AND js.je = jd.je
> > AND js.je_sub = jd.je_sub
> > AND jd.je_ccn = xref.je_ccn
> > AND jd.je = xref.je
> > AND jd.je_sub = xref.je_sub
> > AND jd.je_line = xref.je_line
> > AND xref.ccn = act.ccn
> > AND xref.project_id = act.project_id
> > AND xref.task_id = act.task_id
> > AND xref.pmctact_seq = act.pmctact_seq
> > AND (js.je_sub,jd.je_line,act.cost_amt) in
> > ( select mm.je_sub,mm.je_line,mm.fim_amt
> > FROM prod.fim_mm mm,
> > prod.rec_hist rh
> > WHERE mm.je_ccn = '07'
> > AND mm.je = 'MM'
> > AND mm.je_sub like jd.je_sub
> > AND mm.je_line like jd.je_line
> > AND mm.fim_amt = act.cost_amt
> > AND mm.je_ccn = rh.ccn(+)
> > AND mm.mas_loc = rh.mas_loc(+)
> > AND mm.ref = rh.receiver(+)
> > AND mm.ref_line = rh.rec_line(+)
> > AND rh.agc = 'LLM'
> > )
> > AND act.application = 'MM'
> > AND SUBSTR(act.gl_acct, 1, 6) IN ('123456','654321')
> > AND js.asof_date BETWEEN TO_DATE('03/31/2006', 'MM/DD/YYYY') + 1 AND
> > TO_DATE('04/30/2006', 'MM/DD/YYYY')
> > AND act.project_id = 'A123'
> > AND act.task_id = ' 3'
> > group by jc.llm)
> > set foo = bar;
> >
> > Note that the select runs fine and returns 2 rows. Any method to get
> > this update to work will be a huge help.
>
 |
 |