complex update

From: <utefan001_at_gmail.com>
Date: 10 Jul 2006 22:03:01 -0700
Message-ID: <1152594181.720979.218290@h48g2000cwc.googlegroups.com>

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. Received on Tue Jul 11 2006 - 00:03:01 CDT