Re: complex update

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.

I agree with Mark ... this makes no sense. What is the business case that describes what you are trying to do?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org