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.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 ;)|||Ex-spurt

a former drip under pressure.....|||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!! :D

THANKS!|||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.|||Post what you have...it'll be easier for to to interprete...

You can have only 1 ELSE...|||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|||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!!|||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?