create or replace function f_vsota_artiklov (par_warehouse_id in tbl_warehouses.warehouse_id%type)
return temp.total%type
is
retval temp.total%type;
begin
with temp as
(select t3.warehouse_id, sum(t1.quantity*t1.unit_price) total from tbl_order_items t1
join tbl_products t2 on t1.product_id = t2.product_id
join tbl_inventories t3 on t2.product_id = t3.product_id
group by t3.warehouse_id
order by t3.warehouse_id)
select temp.total
into retval
from temp
where t3.warehouse_id = par_warehouse_id
return retval;
end;
/
I have the following code, the temp table returns:
However, I'm not sure why is not returning temp.total or did I define return/retval wrong?
I'm trying to return total based on warehouse_id input