Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2004
    Location
    Ohio
    Posts
    4

    Unanswered: Another question on CASE WHEN THEN....

    My fellow SQL expert is out of the office today, and I need help!

    What is the syntax when you have (2) conditions that both must be met before the result? Ex:

    Commission = CASE WHEN CommissionEvent = 'Portfolio'

    And NewCustomer = Yes

    THEN it does a formula..... and then there's a second set of (2) conditions that both must be met to produce another result.

    WHEN CommissionEvent = 'Portfolio'

    And NewCustomer = No

    THEN it does a different formula. If it doesn't meet either set of conditions, the result should be 0.

    I tried it they way I thought it should work but I am getting an invalid syntax error.

    I hope my question makes sense.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    Commission = CASE WHEN CommissionEvent = 'Portfolio'
                       And NewCustomer = Yes
                      THEN /* it does a formula */
                      WHEN CommissionEvent = 'Portfolio'
                       And NewCustomer = No
                      THEN /* it does a different formula */
                      ELSE 0
                 END
    you too can be an SQL expert
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Ex-spurt


    a former drip under pressure......
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jan 2004
    Location
    Ohio
    Posts
    4
    Well shoot, I was putting in THEN WHEN where the AND should go-- DUH! I should have just typed it just like in my question!

    I was trying to word it similarly to how an IF THEN ELSE statement goes which would have read "THEN IF" in that spot so I thought "THEN WHEN" would have been the substitution.

    I like that definition of expert, Brett!!

    THANKS!

  5. #5
    Join Date
    Jan 2004
    Location
    Ohio
    Posts
    4

    It's sort of working.....

    But I need another ELSE 0 somewhere for if the first statement evaluates as false. I'm getting the 0 when the

    CommissionEvent = 'Portfolio' and NewCustomer field = No

    but a NULL when the

    CommissionEvent <> 'Portfolio' and the NewCustomer field = No.

    I tried to put an additional ELSE 0 next to the ending ELSE 0 but got a syntax error. I also tried to put the ELSE 0 before the 2nd WHEN statement but also got a syntax error.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Post what you have...it'll be easier for to to interprete...

    You can have only 1 ELSE....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: It's sort of working.....

    Originally posted by notasoccermom
    But I need another ELSE 0 somewhere for if the first statement evaluates as false. I'm getting the 0 when the

    CommissionEvent = 'Portfolio' and NewCustomer field = No

    but a NULL when the

    CommissionEvent <> 'Portfolio' and the NewCustomer field = No.

    I tried to put an additional ELSE 0 next to the ending ELSE 0 but got a syntax error. I also tried to put the ELSE 0 before the 2nd WHEN statement but also got a syntax error.
    What about this version?

    Commission = CASE WHEN CommissionEvent = 'Portfolio'
    then case
    when NewCustomer = Yes
    THEN /* it does a formula */
    when NewCustomer = No
    THEN /* it does a different formula */
    else 0
    end
    ELSE 0
    END

  8. #8
    Join Date
    Jan 2004
    Location
    Ohio
    Posts
    4
    Never mind!! I just figured it out. I had a formula with an alias name in my view that I was trying to use in this CASE statement, but I also had that same column name in one of my tables. So it was pulling the data from my table with that column name which had null values, so I had to rename my alias to something else and use the actual formula for the alias instead of the alias name in the CASE statement. Now it works fine!

    Sometimes you just wrack your brain so hard on something that you can't see the forest for the trees, or the trees for the forest! And what I thought was the problem really wasn't at all!

    Thanks for everyone's help, I'll have to keep this forum in mind when I can't get answers at work!!

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by notasoccermom
    I'll have to keep this forum in mind when I can't get answers at work!!
    You get answers at work?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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