July 2, 2003 at 10:34 am
Can someone please tell me why this CASE statement isnt working?
'Query' = case textdata when left(cast(textdata as varchar(4)), 4) = 'exec'
then substring(textdata, 6, charindex(' ', textdata) else textdata end,
The error I get is:
Incorrect syntax near '=' (the one next to 'exec')
TIA
July 2, 2003 at 12:18 pm
try this in your select:
(
CASE
WHEN LEFT(CAST(textdata AS VARCHAR(4)), 4) = 'exec' THEN
SUBSTRING(textdata, 6, CHARINDEX(' ', textdata)
ELSE textdata
END
) AS Query
July 3, 2003 at 7:53 am
jpipes' revision should work. Here's the why.
There are essentially two forms for the CASE statement. The first (jpipes example) is based on the programming standard:
CASE
<if condition A do this>
<otherwise if condition B do that>
<otherwise if none of the above do the other thing>
And the other form (what you tried to use) is kind of like a "switch" value:
CASE X
<if X = a do this>
<otherwise if X = b do that>
<otherwise if none of the above do the other thing>
Now, with the "switch" structure, you state the value to be considered (textdata, in your problem), and in all the subsequent WHEN statements it is assumed you are comparing the value, the whole value, and nothing but the value. However, since you need to perform further functions/analysis on the value in subsequent lines, you have to use the more complex structure, where you spell out the value being evaluated in each line.
As for column alias assignment,
either form
'Query' =
AS Query
works.
Philip
July 8, 2003 at 9:57 am
Interesting, I had wondered what the diff was. Thank you very much.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply