Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2008
    Posts
    16

    Unanswered: multiple select conditions

    Hi

    I'm trying to retrieve multiple calculated fields (ala excel) from the same table each with its own conditions. Consider the following two select tables:

    Code:
    SELECT (GMM_DRY_LAB_TX1/(GMM_DRY_LAB_TX1+GMM_WET_LAB_TX1-GMM_SMP_LAB_TX1)) AS GR_LAB_TX1 FROM GMM WHERE (GMM_DRY_LAB_TX1+GMM_WET_LAB_TX1-GMM_SMP_LAB_TX1)<>0
    SELECT (GMM_DRY_LAB_TX2/(GMM_DRY_LAB_TX2+GMM_WET_LAB_TX2-GMM_SMP_LAB_TX2)) AS GR_LAB_TX2 FROM GMM WHERE (GMM_DRY_LAB_TX2+GMM_WET_LAB_TX2-GMM_SMP_LAB_TX2)<>0
    How would one combine these two select stmts to output GR_LAB_TX1,GR_LAB_TX2

    The code below doesn't work for me as the AND statement may eliminate one result if the other is not satisfied and vice versa.

    Code:
    SELECT (GMM_DRY_LAB_TX1/(GMM_DRY_LAB_TX1+GMM_WET_LAB_TX1-GMM_SMP_LAB_TX1)) AS GR_LAB_TX1,
    (GMM_DRY_LAB_TX2/(GMM_DRY_LAB_TX2+GMM_WET_LAB_TX2-GMM_SMP_LAB_TX2)) AS GR_LAB_TX2
    FROM GMM
    WHERE
    (GMM_DRY_LAB_TX1+GMM_WET_LAB_TX1-GMM_SMP_LAB_TX1)<>0 AND
    (GMM_DRY_LAB_TX2+GMM_WET_LAB_TX2-GMM_SMP_LAB_TX2)<>0
    Not sure I explained this correctly. Condition 1 should apply to the first select and independently condition 2 applies to the second regardless if the first is true. Any suggestions how to tackle this?
    Last edited by Andre Smit; 09-11-08 at 13:49.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Long question, short answer:

    Change the AND to OR
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jul 2008
    Posts
    16
    Using OR wont work since a division by zero may occur if all the conditions are not met.

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    put the value you are selecting in a CASE to check for the div0 case.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - the where condition is right.

    Change your denominators a la:
    Code:
    ..../NULLIF(GMM_DRY_LAB_TX2+GMM_WET_LAB_TX2-GMM_SMP_LAB_TX2, 0).....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by jezemine
    put the value you are selecting in a CASE to check for the div0 case.
    Pffff - if you get paid by the keystroke
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jul 2008
    Posts
    16
    Thanks guys

    Both work - pootle's solution is a surprise - I've never used NULLIF b4

    Here's the code

    Code:
    SELECT
    CASE
    WHEN (GMM_DRY_LAB_TX1+GMM_WET_LAB_TX1-GMM_SMP_LAB_TX1)<>0 THEN
    (GMM_DRY_LAB_TX1/(GMM_DRY_LAB_TX1+GMM_WET_LAB_TX1-GMM_SMP_LAB_TX1))
    END AS GR_LAB_TX1, 
    CASE
    WHEN (GMM_DRY_LAB_TX2+GMM_WET_LAB_TX2-GMM_SMP_LAB_TX2)<>0 THEN
    (GMM_DRY_LAB_TX2/(GMM_DRY_LAB_TX2+GMM_WET_LAB_TX2-GMM_SMP_LAB_TX2))
    END AS GR_LAB_TX2,
    CASE
    WHEN (GMM_DRY_LAB_TX3+GMM_WET_LAB_TX3-GMM_SMP_LAB_TX3)<>0 THEN
    (GMM_DRY_LAB_TX3/(GMM_DRY_LAB_TX3+GMM_WET_LAB_TX3-GMM_SMP_LAB_TX3))
    END AS GR_LAB_TX3
    .
    .
    .
    FROM GMM 
    
    SELECT
    GMM_DRY_LAB_TX1/NULLIF(GMM_DRY_LAB_TX1+GMM_WET_LAB_TX1-GMM_SMP_LAB_TX1,0) AS GR_LAB_TX1,
    GMM_DRY_LAB_TX2/NULLIF(GMM_DRY_LAB_TX2+GMM_WET_LAB_TX2-GMM_SMP_LAB_TX2,0) AS GR_LAB_TX2,
    GMM_DRY_LAB_TX3/NULLIF(GMM_DRY_LAB_TX3+GMM_WET_LAB_TX3-GMM_SMP_LAB_TX3,0) AS GR_LAB_TX3
    .
    .
    .
    FROM GMM

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Andre Smit
    pootle's solution is a surprise
    You are no more surprised than me - I get one right about every 3000 posts: ask anyone.

    Glad they worked out for you.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Wow, I wonder if that means that I'm due to get one right soonish.

    -PatP

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, i have had three or four correct myself too

    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
  •