May 11, 2006 at 12:20 pm
CASE WHEN ProductID=1 THEN 'P1'
WHEN ProductID=2 THEN 'P2'
WHEN ProductID=3 THEN 'P3'
WHEN ProductID=4 THEN 'P4'
WHEN ProductID=5 THEN 'P5'
WHEN ProductID=6 THEN 'P1'
WHEN ProductID=7 THEN 'P7'
WHEN ProductID=8 THEN 'P8'
WHEN ProductID=9 THEN 'P9'
WHEN ProductID=10 THEN 'P10'
WHEN ProductID=11 THEN 'P11'
ELSE 'PN'
END AS P
FROM Northwind.dbo.Products
May 11, 2006 at 12:56 pm
Basic syntax:
if x
begin
--do what you want here
end
else if y
begin
--do what you want here
end
else
begin
--do what you want here
end
More importantly, what are you trying to accomplish? As far as I know (and I have been known to be wrong), if statements aren't allowed within a SELECT.
May 11, 2006 at 1:44 pm
Yeah, what do you mean a series of statements?
what are you trying to accomplish?
May 11, 2006 at 2:34 pm
i want to be able use any other syntax to produce the same results as this case expression but without using a case expression.
May 11, 2006 at 2:41 pm
Why?
Case was designed to do this. Anything else would be much more complicated.
May 11, 2006 at 2:51 pm
Because! Case expressions can not be nested more than 10 levels if the query is against a linked server.
Server: Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Server: Msg 125, Level 15, State 1, Line 1
Case expressions may only be nested to level 10.
May 11, 2006 at 3:15 pm
select ProductID,ProductName,SupplierID,
CASE WHEN ProductID < 11 THEN 'P' + CONVERT(varchar(2),ProductID)
ELSE 'PN'
END As NewP
FROM Northwind.dbo.Products
May 11, 2006 at 3:28 pm
Ah, now that's a bit more helpful. Did a little MSDN searching and came up with the following:
http://forums.microsoft.com/msdn/showpost.aspx?postid=260352&siteid=1
I would agree with the poster - put the values into a temp table and do the case statement on that.
May 11, 2006 at 4:05 pm
YOU GUYS/CHICKS TOTALLY ROCK! Thanks!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply