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

    Unanswered: need help in using case in select statement

    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 :

    Code:
    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
    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 @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:

    Code:
    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

  2. #2
    Join Date
    Mar 2004
    Posts
    480

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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