Hi
With online qty and str qty, I'm having trouble calculating the quantity.
I tried the data computation with one record, and it worked perfectly with online qty and str qty. However, when I attempted it with several records(same items), it did not work. need guidance on this
I've written the following query, so if I'm wrong on how to design the select statement or if somebody is utilising the cursor approach, please give specifics.
**Query :**
with final_order as (
select 121212 po,11111 item ,20 sw_need,30 order_det from dual union all
select 131313 po,22222 item ,50 sw_need,30 order_det from dual union all
select 141414 po,33333 item ,50 sw_need,40 order_det from dual union all
select 151515 po,33333 item ,50 sw_need,30 order_det from dual union all
select 161616 po,44444 item ,50 sw_need,40 order_det from dual union all
select 171717 po,44444 item ,50 sw_need,5 order_det from dual)
select item,
case
when sw_need<order_det then sw_need
when sw_need>order_det then order_det-1
else 0
end online_qty,
case
when sw_need<order_det then order_det-sw_need
when sw_need>order_det then (order_det) -(order_det-1)
else 0
end str_qty
from final_order
**expected results**
with final_order as (
select 121212 po,11111 item ,20 sw_need,30 order_det, 20 online_qty,10 store_qty from dual union all
select 131313 po,22222 item ,50 sw_need,30 order_det, 29 online_qty,1 store_qty from dual union all
select 141414 po,33333 item ,50 sw_need,40 order_det, 40 online_qty,0 store_qty from dual union all
select 151515 po,33333 item ,50 sw_need,30 order_det, 10 online_qty,20 store_qty from dual union all
select 141414 po,44444 item ,50 sw_need,40 order_det, 40 online_qty,0 store_qty from dual union all
select 151515 po,44444 item ,50 sw_need,5 order_det, 4 online_qty,1 store_qty from dual)
select * from final_order