Results 1 to 2 of 2

Thread: Need help in case statement

  1. #1
    Join Date
    Oct 2011

    Need help in case statement


    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)
    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

    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
    (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!
    Knowledge is that which can be shown to be the case, and Intelligence is the method one uses to deploy the demonstration of what is the case, everything else is Information.

    For a stronger Text reading try:

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