Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2010
    Posts
    136

    Unanswered: Need help in case statement

    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

    Code:
    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..
    Attached Thumbnails Attached Thumbnails 1500 requirement qty need output.JPG   1500 requirement qty with lack output.JPG   900 requirement qty.JPG   2500 requirement qty.JPG  

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

  3. #3
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    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.
    It would be easier for someone to understand if you showed exactly what was the problem. . . Posting "it didn't work" or "i got a problem" is probably the biggest waste of time on the forum. . . Posting those thumbnails with no explanation doesn't help much either.

    Why not simply tell us what actualy was the output and what you wanted instead?

    Hopefully you have discovered that the "shotgun approach" (posting in several places the same question) doesn't help you much. It will (however) draw some flames.

  4. #4
    Join Date
    Dec 2010
    Posts
    136
    Quote Originally Posted by papadi View Post
    It would be easier for someone to understand if you showed exactly what was the problem. . . Posting "it didn't work" or "i got a problem" is probably the biggest waste of time on the forum. . . Posting those thumbnails with no explanation doesn't help much either.

    Why not simply tell us what actualy was the output and what you wanted instead?

    Hopefully you have discovered that the "shotgun approach" (posting in several places the same question) doesn't help you much. It will (however) draw some flames.
    I'm sorry...
    The problem is i cant get data when the requirment qty is less that output qty

  5. #5
    Join Date
    Dec 2010
    Posts
    136
    I attach the sample output..and the problem is only 1 data displayed when the requirment qty is less that output qty and greater than balance qty...

    Thank you

  6. #6
    Join Date
    Dec 2010
    Posts
    136
    When the requirement qty is less than balance qty the qty = requirement qty
    When the requirement qty is greater than balance qty the qty = balance qty and qty from output qty to complete the requirement qty
    in this two condition the qty is correct.

    and when the requirement qty is greater than balance qty and requirement is less than output qty the qty should be balance qty and qty from output qty to complete the requirement qty

    for example my requirement qty is 1500

    the output should be
    966 from balance qty
    534 from output qty 2000
    to complete the 1500 requirement qty..


    I hope it help for your understanding of my problem

    Thank you so much

  7. #7
    Join Date
    Dec 2010
    Posts
    136
    I was so confused why when the requirement qty is greater that output qty the qty is correct he gets the balance qty and the qty that he needs from output qty to complete his requirement qty, but when the requirement qty is less than output qty he only gets the balance qty


    Thank you so much..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •