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
