Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a case when statement in a extract query which doesn't seem to be returning what i'm expecting

DECLARE @Val1 int, @Val2 int,@Val3 int, @Val4 int, @Val5 int

SELECT  @Val1 = 5 , @Val2= 2,@Val3= 200000, @Val5  = 2,@Val4 = 2014
SELECT  @Val1 , @Val2,@Val3, @Val4, @Val5   

SELECT CASE

        WHEN  @Val1 = 5 AND @Val2  IN(1,2) AND @Val3 = 200000 AND @Val4 IS NULL  THEN 'ReturnValue1'
        WHEN  @Val1 = 5 AND @Val2  IN(1,2) AND @Val3 = 200000 AND @Val4 IS NOT NULL THEN 'ReturnValue2'
        WHEN  @Val1 = 5 AND @Val2  = 2 AND @Val3 = 200000  AND @Val5 = 2 AND @Val4 IS NOT NULL THEN 'ReturnValue3'
        ELSE NULL
    END [TestingValue]

I'm expecting the value to be ReturnValue3 however it appears to be ignoring this whole line for some reason.

Can anyone see a problem which I'm not spotting.

share|improve this question
    
And gives you 'ReturnValue2' as result, because the 2nd condition is true?: WHEN @Val1 = 5 AND @Val2 IN(1,2) AND @Val3 = 200000 AND @Val4 IS NOT NULL THEN 'ReturnValue2' –  ypercube Feb 11 at 13:32
1  
This has been cross-posted on Stack Overflow stackoverflow.com/questions/21703156/… If you feel that DBA.SE is a better fit for this question then you should flag it for mod attention asking that they move it to an appropriate site. –  bluefeet Feb 11 at 13:35

1 Answer 1

Your second clause is triggering as true before your third clause and as such your third clause does not get evaluated - CASE WHEN returns the first TRUE value as opposed to the last TRUE value

@Val1 = 5 TRUE

@Val2 IN(1,2) TRUE

@Val3 = 200000 TRUE

@Val4 IS NOT NULL TRUE

share|improve this answer
    
Thanks, I had notice if I moved the statement to the top then it work fine, but thought I was missing something else. –  SQLGelder Feb 11 at 13:37

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.