www.webdeveloper.com
Results 1 to 2 of 2

Thread: Need help in case statement

  1. #1
    Join Date
    Oct 2011
    Posts
    350

    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 Images Attached Images

  2. #2
    Join Date
    Feb 2012
    Location
    youTUBE
    Posts
    234

    Case order.

    Sql will not try all cases then use the "best" match. The first matched case statement will win the round. Place the most constrained case before the others, giving it top priority, then proceed until the loosest constraint will have the lowest priority.

    What I mean is, specific ranged values should be the first to be evaluated in the sense that the following order would be functionally incorrect:
    (1) a < B
    (2) a < B AND p < Q
    but
    (1) a < B AND p < Q
    (2) a < B
    would give proper results.

    Check if this helps, because yoiu have a lot of Sql to sift through!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles