Error with a merge statement
Please help with this error:
merge into cat_ord_sm a
*
ERROR at line 1:
ORA-00913: too many values
Here is the SQL:
merge into cat_ord_sm a
using
(select c_key, cat_key, p_date, ord_type, source_key, state_key,
sum(demand_qty) as d_qty, sum(demand_cost) as d_cost, sum(demand_retail) as d_retail,
sum(can_qty) as c_qty, sum(can_cost) as c_cost, sum(can_retail) as c_retail,
sum(ship_qty) as s_qty, sum(ship_cost) as s_cost, sum(ship_retail) as s_retail,
sum(ret_qty) as r_qty, sum(ret_cost) as r_cost, sum(ret_retail) as r_retail,
sum(ord_count) as o_count, sum(ord_xchg_count) as ox_count from cat_ord_sm_add
group by c_key, cat_key, p_date, ord_type, source_key, state_key) b
on (a.c_key = b.c_key
and a.cat_key = b.cat_key
and a.p_date = b.p_date
and a.ord_type = b.ord_type
and a.source_key = b.source_key
and a.state_key = b.state_key)
when matched then update set
a.demand_qty = a.demand_qty + b.d_qty,
a.demand_cost = a.demand_cost + b.d_cost,
a.demand_retail = a.demand_retail + b.d_retail,
a.can_qty = a.can_qty + b.c_qty,
a.can_cost = a.can_cost + b.c_cost,
a.can_retail = a.can_retail + b.c_retail,
a.ship_qty = a.ship_qty + b.s_qty,
a.ship_cost = a.ship_cost + b.s_cost,
a.ship_retail = a.ship_retail + b.s_retail,
a.ret_qty = a.ret_qty + b.r_qty,
a.ret_cost = a.ret_cost + b.r_cost,
a.ret_retail = a.ret_retail + b.r_retail,
a.ord_count= a.ord_count + b.o_count,
a.ord_xchg_count = a.ord_xchg_count + b.ox_count
when not matched then insert
(a.c_key, a.cat_key, a.p_date, a.ord_type. a.source_key, a.state_key,
a.demand_qty, a.demand_cost, a.demand_retail, a.can_qty, a.can_cost, a.can_retail,
a.ship_qty, a.ship_cost, a.ship_retail, a.ret_qty, a.ret_cost, a.ret_retail,
a.ord_count, a.ord_xchg_count)
values
(b.c_key, b.cat_key, b.p_date, b.ord_type, b.source_key, b.state_key,
b.d_qty, b.d_cost, b.d_retail, b.c_qty, b.c_cost, b.c_retail,
b.s_qty, b.s_cost, b.s_retail, b.r_qty, b.r_cost, b.r_retail,
b.o_count, b.ox_count)
;
Your input is highly appreciated.
Chintzs
