October 13, 2009 at 7:07 pm
This works:
select case
when opseq=66 then 'LowTemp'
when opseq In (1015,1018,1021,1024,2015,2018,2021,2024,27,30) then 'SMT'
else [cell]
end
as exp1
FROM ProductInfoMaster INNER JOIN OppsInfoFromJoints ON
(ProductInfoMaster.Noun = OppsInfoFromJoints.Noun)
AND (ProductInfoMaster.Rev = OppsInfoFromJoints.Rev)
This doesn't:
select case opseq
when 66 then 'LowTemp'
when In (1015,1018,1021,1024,2015,2018,2021,2024,27,30) then 'SMT'
else [cell]
end
as exp1
FROM ProductInfoMaster INNER JOIN OppsInfoFromJoints ON
(ProductInfoMaster.Noun = OppsInfoFromJoints.Noun)
AND (ProductInfoMaster.Rev = OppsInfoFromJoints.Rev)
I get error "Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'In'."
The two statemenst seem like they should be equivalent to me. But I the 2nd one seems a bit clearer. Can someone explain to me why it doesn't work?
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]
October 13, 2009 at 9:29 pm
In the BOL the definition for the case statement
Simple CASE function:
CASE input_expression
WHEN when_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END
Searched CASE function:
CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END
And the “in” is not the valid when experession, so that the error occurred,
And try this
select case opseq
when 66 then 'LowTemp'
when 1015 then 'SMT'
when 1018 then 'SMT'
when 1021 then 'SMT'
when 1024 then 'SMT'
...
...
October 16, 2009 at 10:43 am
So, I'm just curious to know why the "in" statement works in one version and not the other. I can't see anything in the definition of the case statement that would tell me this. What am I missing?
[font="Comic Sans MS"]She Through Whom All Data Flows[/font]
October 17, 2009 at 10:51 pm
You attempted to use the Simple Case Function where you needed to use the Searched Case Function.
If you look at the BOL syntax explanation for each Case type, you'll see that they are different. The IN keyword evaluates to a boolean value so it must be used in the Searched Case Funtion. For the IN to pass syntax check, it must have values on both sides of the IN keyword. Look up IN in BOL.
For your second example to work, you'd need to scrap the IN keyword and break each value up into it's own WHEN statement like this:
select case opseq
when 66 then 'LowTemp'
when 1015 then 'SMT'
when 1018 then 'SMT'
when 1021 then 'SMT'
when 1024 then 'SMT'
when 2015 then 'SMT'
when 2018 then 'SMT'
when 2021 then 'SMT'
when 2024 then 'SMT'
when 27 then 'SMT'
when 30 then 'SMT'
else [cell]
end
as exp1
FROM ProductInfoMaster INNER JOIN OppsInfoFromJoints ON
(ProductInfoMaster.Noun = OppsInfoFromJoints.Noun)
AND (ProductInfoMaster.Rev = OppsInfoFromJoints.Rev)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply