• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 651
  • Last Modified:

update statement based on "with clause" returns error

Hello pros! I have a short question as I am stuck with a problem.

I have a query based on a with clause and would like to use it for an update statement.
I recieve the error message that an Update query must include a subquery.

Can you give me some help please or ist this type of construction not working at all?

Brgds,

Seb
update tbl_matrix_custom_eval f SET (f.ba_kat, f.bemerkung) = 

with w as ((select
x.lng_baumart,
b.ba_lang,
x.ba_kat,
decode(b.ba_gruppe,1,'Ba-Grp. 1',2,'Ba-Grp. 2',3,'Ba-Grp 3',4,'Ba-Grp. 4',5,'Ba-Grp. 5',6,'Ba-Grp. 6',7,'Ba-Grp. 7',8,'Ba-Grp. 8') ba_gruppe, x.flag, 0 disable_status
from 
vt_tbl_bestand v,
vt_punktdaten_join p,
vt_tbl_baumart b,
vt_tbl_matrix x
where 
(p.cnt_gebiet = :p2008_cnt_gebiet)
and (v.lng_baumart = b.cnt_baumart) 
and (v.lng_inv_pt_id = p.inv_pt_id_sub)
and (p.str_lrt_main = :p2008_str_lrt)
and (p.int_status_sub = 3)
and (p.int_be_main = :p2008_be)
and (p.int_wg = :p2008_wg)
and (x.lrt_class=:p2008_str_lrt_mx)
and (x.lng_baumart=v.lng_baumart)
--and x.ba_kat in ('S','B','H','P','N')
group by 
b.ba_lang,
x.lng_baumart,
x.ba_kat,
ba_gruppe,
x.flag)
union 
(select
x.lng_baumart,
b.ba_lang,
x.ba_kat,
decode(b.ba_gruppe,1,'Ba-Grp. 1',2,'Ba-Grp. 2',3,'Ba-Grp 3',4,'Ba-Grp. 4',5,'Ba-Grp. 5',6,'Ba-Grp. 6',7,'Ba-Grp. 7',8,'Ba-Grp. 8') ba_gruppe, x.flag, 0 disable_status
from 
vt_tbl_verjuengung v,
vt_punktdaten_join p,
vt_tbl_baumart b,
vt_tbl_matrix x
where 
(p.cnt_gebiet = :p2008_cnt_gebiet)
and (v.lng_baumart = b.cnt_baumart) 
and (v.lng_inv_pt_id = p.inv_pt_id_sub)
and (p.str_lrt_main = :p2008_str_lrt)
and (p.int_status_sub = 3)
and (p.int_be_main = :p2008_be)
and (p.int_wg = :p2008_wg)
and (x.lrt_class=:p2008_str_lrt_mx)
and (x.lng_baumart=v.lng_baumart)
--and x.ba_kat in ('S','B','H','P','N')
))

-- This is the subquery for the update 

(select ba_kat, bemerkung from (
((select w.lng_baumart, w.ba_kat, w.ba_gruppe, w.flag, w.disable_status, null bemerkung from w)
union all
(select d.lng_baumart, d.ba_kat,
decode(r.ba_gruppe,1,'Ba-Grp. 1',2,'Ba-Grp. 2',3,'Ba-Grp 3',4,'Ba-Grp. 4',5,'Ba-Grp. 5',6,'Ba-Grp. 6',7,'Ba-Grp. 7',8,'Ba-Grp. 8') ba_gruppe, d.flag, 
case when d.lng_baumart = 78 and d.lng_baumart not in (select w.lng_baumart from w where w.lng_baumart = 62) then 1
when d.lng_baumart = 74 and d.lng_baumart not in (select w.lng_baumart from w where w.lng_baumart in (5,6,7,8,9,81)) then 1
when d.lng_baumart = 72 and d.lng_baumart not in (select w.lng_baumart from w where w.lng_baumart in (22,23,24,25,26,27)) then 1 
when d.lng_baumart = 71 and d.lng_baumart not in (select w.lng_baumart from w where w.lng_baumart in (46,47)) then 1
when d.lng_baumart = 54 and d.lng_baumart not in (select w.lng_baumart from w where w.lng_baumart in (13,14,15)) then 1
when d.lng_baumart = 53 and d.lng_baumart not in (select w.lng_baumart from w where w.lng_baumart in (28,29,30,31,32,33,79)) then 1
else null end as disable_status, 'Diese BA ist LRT-typisch, jedoch nicht im LRT vertreten!' bemerkung
from vt_tbl_baumart r, vt_tbl_matrix d
where 
r.cnt_baumart = d.lng_baumart
and (d.lrt_class = :p2008_str_lrt_mx)
and d.ba_kat in ('S','B','H','P','N') and d.lng_baumart not in (select 
w.lng_baumart from w))))z) where z.lng_baumart = f.lng_baumart and f.lng_gebiet = :p2008_cnt_gebiet and f.lrt_class = :p2008_str_lrt and f.int_wg = :p2008_wg and f.int_be = :p2008_be and eval_type = 1;

Open in new window

0
skahlert2010
Asked:
skahlert2010
  • 3
  • 3
1 Solution
 
LowfatspreadCommented:
the with constructs always precede the actual statement that you want to execute..

think define the vuew then use it

with w as (....)
update...
0
 
MilleniumaireCommented:
You can't use the with query in both the set and where clause as they are different queries and are therefore not related/connected.

For example, if you take a simple update query:

update mytable
set col1 = (select x from mytable2 where a = b)
where exists (select y from mytable3 where z = x);

In this case the 2 sub queries and completeley independant of each other.  You can't refer to table mytable2 in the sub query in the where clause and likewise you can't refer to mytable3 in the sub query in the set clause.

The same applies to defining a sub query using the with clause, you can reference the named query in a query that is part of another clause in the update statement.

Hope this is clear.
0
 
skahlert2010Author Commented:
Well I wrote several fully functional insert statements where the with as declaration followed the insert command.

I just modified my code as you suggested and receive an error: "Keyword SELECT is missing"!
No line is mentioned in my debugger.

Any idea?


with w as ((select
x.lng_baumart,
b.ba_lang,
x.ba_kat,
decode(b.ba_gruppe,1,'Ba-Grp. 1',2,'Ba-Grp. 2',3,'Ba-Grp 3',4,'Ba-Grp. 4',5,'Ba-Grp. 5',6,'Ba-Grp. 6',7,'Ba-Grp. 7',8,'Ba-Grp. 8') ba_gruppe, x.flag, 0 disable_status
from 
vt_tbl_bestand v,
vt_punktdaten_join p,
vt_tbl_baumart b,
vt_tbl_matrix x
where 
(p.cnt_gebiet = :p2008_cnt_gebiet)
and (v.lng_baumart = b.cnt_baumart) 
and (v.lng_inv_pt_id = p.inv_pt_id_sub)
and (p.str_lrt_main = :p2008_str_lrt)
and (p.int_status_sub = 3)
and (p.int_be_main = :p2008_be)
and (p.int_wg = :p2008_wg)
and (x.lrt_class=:p2008_str_lrt_mx)
and (x.lng_baumart=v.lng_baumart)
--and x.ba_kat in ('S','B','H','P','N')
group by 
b.ba_lang,
x.lng_baumart,
x.ba_kat,
ba_gruppe,
x.flag)
union 
(select
x.lng_baumart,
b.ba_lang,
x.ba_kat,
decode(b.ba_gruppe,1,'Ba-Grp. 1',2,'Ba-Grp. 2',3,'Ba-Grp 3',4,'Ba-Grp. 4',5,'Ba-Grp. 5',6,'Ba-Grp. 6',7,'Ba-Grp. 7',8,'Ba-Grp. 8') ba_gruppe, x.flag, 0 disable_status
from 
vt_tbl_verjuengung v,
vt_punktdaten_join p,
vt_tbl_baumart b,
vt_tbl_matrix x
where 
(p.cnt_gebiet = :p2008_cnt_gebiet)
and (v.lng_baumart = b.cnt_baumart) 
and (v.lng_inv_pt_id = p.inv_pt_id_sub)
and (p.str_lrt_main = :p2008_str_lrt)
and (p.int_status_sub = 3)
and (p.int_be_main = :p2008_be)
and (p.int_wg = :p2008_wg)
and (x.lrt_class=:p2008_str_lrt_mx)
and (x.lng_baumart=v.lng_baumart)
--and x.ba_kat in ('S','B','H','P','N')
))

update tbl_matrix_custom_eval f SET (f.ba_kat, f.bemerkung) = 
-- This is the subquery for the update 
(select ba_kat, bemerkung from (
((select w.lng_baumart, w.ba_kat, w.ba_gruppe, w.flag, w.disable_status, null bemerkung from w)
union all
(select d.lng_baumart, d.ba_kat,
decode(r.ba_gruppe,1,'Ba-Grp. 1',2,'Ba-Grp. 2',3,'Ba-Grp 3',4,'Ba-Grp. 4',5,'Ba-Grp. 5',6,'Ba-Grp. 6',7,'Ba-Grp. 7',8,'Ba-Grp. 8') ba_gruppe, d.flag, 
case when d.lng_baumart = 78 and d.lng_baumart not in (select w.lng_baumart from w where w.lng_baumart = 62) then 1
when d.lng_baumart = 74 and d.lng_baumart not in (select w.lng_baumart from w where w.lng_baumart in (5,6,7,8,9,81)) then 1
when d.lng_baumart = 72 and d.lng_baumart not in (select w.lng_baumart from w where w.lng_baumart in (22,23,24,25,26,27)) then 1 
when d.lng_baumart = 71 and d.lng_baumart not in (select w.lng_baumart from w where w.lng_baumart in (46,47)) then 1
when d.lng_baumart = 54 and d.lng_baumart not in (select w.lng_baumart from w where w.lng_baumart in (13,14,15)) then 1
when d.lng_baumart = 53 and d.lng_baumart not in (select w.lng_baumart from w where w.lng_baumart in (28,29,30,31,32,33,79)) then 1
else null end as disable_status, 'Diese BA ist LRT-typisch, jedoch nicht im LRT vertreten!' bemerkung
from vt_tbl_baumart r, vt_tbl_matrix d
where 
r.cnt_baumart = d.lng_baumart
and (d.lrt_class = :p2008_str_lrt_mx)
and d.ba_kat in ('S','B','H','P','N') and d.lng_baumart not in (select 
w.lng_baumart from w))))z) where z.lng_baumart = f.lng_baumart and f.lng_gebiet = :p2008_cnt_gebiet and f.lrt_class = :p2008_str_lrt and f.int_wg = :p2008_wg and f.int_be = :p2008_be and eval_type = 1;

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
MilleniumaireCommented:
The WITH clause can only be used with SELECT statements so you are correct to preceed the SELECT statement with it as the update statement wouldn't understand the WITH clause.
0
 
skahlert2010Author Commented:
Thanks milleniumaire!

If I understood you correctly there's no way I can use the with clause for my demand? Is that correct?
0
 
MilleniumaireCommented:
Yes, that's correct.

The scope of the query named by the WITH clause does not extend to the 2nd sub query used for the where clause.

You could change the 2nd sub query to use its own WITH clause, but I suspect this would defeat the purpose of you wanting to use a WITH clause as I guess you wanted to define the named query once and use it in both sub-queries.
0
 
skahlert2010Author Commented:
Thanks for your info! Helps a lot! I won't waste more time on that with clause query!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now