Hi All,
I want to update the stock_value table which has the " price number" datatype . here I'm fetching the value from the loop and storing it to the variable then on the update condition applying those values. but I'm not able to update the data and giving the error "PL/SQL: numeric or value error" can anyone suggest howto do that.
declare
v_qty number :=0;
v_price varchar2(4000);
begin
for i in (select * from items_stock) loop
for j in (select * from items where itemid = i.itemid order by rowid desc) loop
v_qty:= v_qty + j.qty;
case when v_price is null then
v_price:= j.price;
when v_price is not null then
v_price:= v_price||','||j.price;
End case;
-- Here doing some logic
End loop;
update stock_value
set qty = 0
where itemid = i.itemid
and price not in '('||v_price||')'; -- here is the error
v_qty:= 0;
v_price := '';
end loop;
end;