need help in using case statement in select statement

[newphpcoder]

Hi...

 

I need to get data from two tables but  i need to based first on jo_last to check if theirs an bal_qty so that I need to get first the bal_qty before I get the output qty from dipping_dump table to get my req qty.

 

here is my query where the rqty is > OUTPUT_QTY :

 

set @t = 0;
set @rqty =2401;
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 @t+d.OUTPUT_QTY > @rqty
THEN (@rqty - @q)
WHEN d.LOT_CODE = j.lotcode AND @rqty > j.bal_qty AND @rqty < d.OUTPUT_QTY
THEN j.bal_qty
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;

 

in this example the output is correct:

 

pcode----code-------------OUTPUT_QTY-DATE_ENTRY------------qty-----cumulative

P35------ 10172710P35- 2000.00-------2012-07-11 09:46:18-966.00-2000.00

P35------ 027723883P35-2000.00------2012-07-11 09:46:44-1435----2000.00

 

this output is correct

this code : 10172710P35 has a balance 966.00 so right that i get first this 966 in jo_last and the lack i get in dipping_dump to get the total req qty 2401.

 

 

and here is the sample where the rqty is < to bal_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 @t+d.OUTPUT_QTY > @rqty
THEN (@rqty - @q)
WHEN d.LOT_CODE = j.lotcode AND @rqty > j.bal_qty AND @rqty < d.OUTPUT_QTY
THEN j.bal_qty
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;

 

 

and the output is correct:

pcode----code-------------OUTPUT_QTY-DATE_ENTRY------------qty-----cumulative

P35------ 10172710P35- 2000.00-------2012-07-11 09:46:18-900.00-2000.00

 

 

as you can see same query i only change the rqty to check if the output is correct.

 

but when i tried this:

 

set @t = 0;
set @rqty =1501;
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 @t+d.OUTPUT_QTY > @rqty
THEN (@rqty - @q)
WHEN d.LOT_CODE = j.lotcode AND @rqty > j.bal_qty AND @rqty < d.OUTPUT_QTY
THEN j.bal_qty
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;

 

the output is:

 

pcode----code-------------OUTPUT_QTY-DATE_ENTRY------------qty-----cumulative

P35------ 10172710P35- 2000.00-------2012-07-11 09:46:18-966.00-2000.00

 

and this correct but lack of output.

 

it should be:

 

pcode----code-------------OUTPUT_QTY-DATE_ENTRY------------qty-----cumulative

P35------ 10172710P35- 2000.00-------2012-07-11 09:46:18-966.00-2000.00

P35------ 027723883P35-2000.00------2012-07-11 09:46:44-535----2000.00

 

to get the 1501 rqty.

 

I tried to fix it, yet the output is wrong so I decided to post my problem in forum.

 

Any help is highly appreciated.

 

Thank you so much