SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what do you mant me to do..to make you read it ..
    Thank you

  3. #3
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this is my query:

    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;
    i need result is:

    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


    but the output of my query is only:
    pcode----code-------------OUTPUT_QTY---DATE_ENTRY------------qty-----cumulative
    P35------ 10172710P35----- 2000.00-------2012-07-11 09:46:18----966.00--2000.00

  4. #4
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by chinasandstone View Post
    ok
    Thank you...

    I tried to resolved it,yet wrong output

  5. #5
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by chinasandstone View Post
    How does this forum signature where settings,

    Thank you
    What do you mean signature settings..
    I'm not familiae on that..

  6. #6
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry...I can't understand what you mean..is it setting in this forum?

  7. #7
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    visit this link:

    http://www.sitepoint.com/forums/show...signature-rule

    I hope you can get the answer for your question..

  8. #8
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, i don't know...I'm not familiar on that...try to search on google...I think you can find the answer on that..

  9. #9
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your welcome...

    I hope you can help me in my problem that i posted...

    Thank you

  10. #10
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)


Bookmarks

Posting Permissions

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