Jump to content

Need help in case statement


Recommended Posts



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, 
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)
@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..





Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.