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..

[cpd]

When you say you get a problem do you mean no data is returned or you get some data but not all your expecting? Or does it just error.

 

Data being returned is dependent on your WHERE clause so if something is being returned but not everything you expect, its because the data your expecting in addition to what's returned doesn't actually match the criteria in the WHERE clause.

[newphpcoder]

Theirs a data output but not  my expected output.

 

 

Thank you

[cpd]

Well then its your WHERE clause not your select data. You should review that section of your code and I'm sure you'll find your problem.

[newphpcoder]

Thank you.