Decrease quantity untill i use all qty

Question

I have table with orders ( example of code http://sqlfiddle.com/#!4/0f284/3 )

ORDER_NO    ITEM    QTY
-----------------------
ORD1    ITEM1       2
ORD1    ITEM2       5
ORD1    ITEM3       7
ORD1    ITEM4       1
ORD2    ITEM1       2
ORD2    ITEM2       2

And i have function that return constatn qty by ORDER_NO

Get_Qty(ORDER_NO)

I want to decreser qty only on statemant (not update table) until i use all qty from function Get_Qty

The result sholud be

ORDER_NO    ITEM    QTY   GET_QTY    QTY_RESULT    
---------------------------------------------------
ORD1    ITEM1       2       10        0 (left 10 - 2 = 8)
ORD1    ITEM2       5       10        0 (left 8 - 5 = 3)
ORD1    ITEM3       7       10        4 (left 3 - 7 = - 4) --if negative use 0
ORD1    ITEM4       1       10        1 (left 0)
ORD2    ITEM1       2       3         0 left (3- 2 = 1)
ORD2    ITEM2       2       3         1 left (1 - 2 = -1) --if negative use 0

select o.order_no,
       o.item,
       o.qty - Get_Qty(order_no)
from  Orders o

Example of code above http://sqlfiddle.com/#!4/0f284/3

Answer 1

You want a cumulative sum to subtract from the total. I think:

select o.order_no, o.item,
       greatest(Get_Qty(order_no) - sum(qty) over (partition by order_no order by id), 0)
from  Orders o;

This subtracts the value by smallest quantity first. Your table doesn't have an ordering column, but you would use that if available.