Need help in case statement

[newphpcoder]

Hi..

I have problem in my select query statement with case statement to compute the qty and I tried evetything to resolved my problem.

here is my query:

first I tried the requirement qty is less than in balance qty.
@rqty = requirement qty
@q= balalce qty

set @t = 0;
set @rqty = 900;
set @r = 563;
set @q = 966;

SELECT SUBSTR(d.LOT_CODE, 9,4) as pcode, d.LOT_CODE as code, d.OUTPUT_QTY,  d.DATE_ENTRY, 
CASE 
WHEN d.LOT_CODE = j.lotcode AND @rqty > j.bal_qty
THEN j.bal_qty
WHEN d.LOT_CODE = j.lotcode AND @rqty < j.bal_qty
THEN @rqty 
WHEN @rqty < @t + d.OUTPUT_QTY
THEN (@rqty - @q)
WHEN @t+d.OUTPUT_QTY > @rqty
THEN (@rqty - @q)
ELSE d.OUTPUT_QTY END as qty,
@t := @t + d.OUTPUT_QTY as cumulative 
FROM dipping_dump d, dependency c, jo_last j
WHERE NOT EXISTS (SELECT j.lotcode, j.pcode FROM jo_dump j WHERE j.lotcode = d.LOT_CODE AND j.pcode = SUBSTR(d.LOT_CODE, 9,4) AND j.qty = d.OUTPUT_QTY) AND c.LOT_CODE = d.LOT_CODE AND c.DATE_ENTRY = d.DATE_ENTRY AND c.TERMINAL_ID = '5' AND c.ACTIVE = '1' AND SUBSTR(d.LOT_CODE, 9,4) = 'P35' AND (@t < @rqty) AND j.STATUS = '1' ORDER BY d.DATE_ENTRY ASC;

I attached the sample image output.

I got a problem when the requirement qty is less than output qty and greater balance qty.

I hope this time someone can understand and help me.

Thank you so much..

[WyCnet]

Sql will not try all cases then use the "best" match. The first matched case statement will win the round. Place the most constrained case before the others, giving it top priority, then proceed until the loosest constraint will have the lowest priority.

What I mean is, specific ranged values should be the first to be evaluated in the sense that the following order would be functionally incorrect:

(1) a < B
(2) a < B AND p < Q

but

(1) a < B AND p < Q
(2) a < B

would give proper results.

Check if this helps, because yoiu have a lot of Sql to sift through!