September 20, 2010 at 12:04 pm
Here is what I want to do:
Select
, CASE
when '1' then 'one' else 1 end as ONE
, CASE
when '2' then 'two' else 2 end as TWO
, CASE
when '3' then 'three' else 3 end as THREE
, ONE + '' + TWO + '' + THREE AS FinishedNumber
it is the concatenated result I am struggling with. How do you concatenate CASE results?????????
thank you in advance!! 😎
September 20, 2010 at 12:53 pm
Most important thing first:
You need to make sure to use the same data type in each part of a CASE statement, otherwise SQL Server will perform an implicit conversion.
In the sample you provided it'll try to convert 'one' to int since 1 is integer and integer data type has a higher precedence then char/varchar.
Back to your original question:
You can concatenate each CASE statement:
DECLARE @t CHAR(1)
SET @t='b'
SELECT
CASE WHEN @t='a' THEN 'y' ELSE 'n' END
+ CASE WHEN @t>'a' THEN 'o' ELSE 'e' END
+ CASE WHEN @t='b' THEN ' ' ELSE 's' END
or wrap it into a CTE:
;
;
WITH cte as
(
SELECT
CASE WHEN @t='a' THEN 'y' ELSE 'n' END AS ONE,
CASE WHEN @t>'a' THEN 'o' ELSE 'e' END AS TWO,
CASE WHEN @t='b' THEN ' ' ELSE 's' END AS THREE
)
SELECT ONE + ' ' + TWO + ' ' + THREE
FROM cte
But always make sure to have the same data type.
September 20, 2010 at 12:58 pm
Couple of things here... It would be helpful if you posted some sample data and expected results as per the first link in my signature. Secondly, It looks like you're taking the character value of '1' and replacing it with text 'one' or if it's not '1' then the integer 1.
It looks like you're going to have all kinds of explicit conversion issues here, and it makes it somewhat hard to determine how to help.
Then looking at your case statement itself, you're not showing the expression you are evaluating in any of your case expressions. Here's a link to the BOL (Books online Sql Server's Help Files) Case Syntax for TSQL. I've never liked the shortened version which it looks like you are trying to use because it makes certain things unclear. (I prefer the true boolean expression syntax).
If I had a table with some text in a column called column one that I needed to evaluate with a Case statement the sample I would provide would look something like this... I've tried to cover a couple of options here because I don't know what you want, see first comment again...
--do this someplace Safe
USE TempDB;
CREATE TABLE #test (
Col1 VARCHAR(10)
);
INSERT INTO #test
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT 'otherstuff' UNION ALL
SELECT 'one' ;
--The below gives a datatype conversion error
--Server: Msg 245, Level 16, State 1, Line 15
--Syntax error converting the varchar value 'one' to a column of data type int.
--It's commented out to all the rest to run'
/*SELECT CASE WHEN col1 = '1' THEN 'one'
ELSE 1 END AS ONE
FROM #test; */
--This resolves it but seems to not be what you want?
SELECT CASE WHEN col1 = '1' THEN 'one'
ELSE '1' END AS ONE
FROM #test;
--To concat these columns together you need to repeat the statement that created the first column of your resultset
SELECT CASE WHEN col1 = '1' THEN 'one'
ELSE '1' END AS ONE,
--Now I'll Concat another column to the first'
CASE WHEN col1 = '1' THEN 'one'
ELSE '1' END + ' ' +
CASE WHEN col1 = '1' THEN 'one'
ELSE '1' END AS two
FROM #test;
--Cleanup
DROP TABLE #test;
Hopefully this will get you in the right direction.
September 20, 2010 at 1:02 pm
Looks like Lutz beat me to it with a bit better sample code... Not something I would have thought to use a CTE for but it would definitely help with readability, I'll have to keep that in mind for the future.
and yeah, I meant implicit conversions not explicit 😉
-Luke.
September 20, 2010 at 1:04 pm
September 20, 2010 at 1:20 pm
@Angelindiego: Glad we could help!! 😀
@luke: 😛 😉
The CTE stuff came to my mind based on Angelindiegos original post. I usually don't use it either... but it definitely makes troubleshooting easier, especially for concatenation without any separator in between.
September 20, 2010 at 2:33 pm
Luke L (9/20/2010)
I've never liked the shortened version [...] because it makes certain things unclear. (I prefer the true boolean expression syntax).
What situations do you find unclear? I prefer the short version when possible, because it's clear that all of your comparisons are against the same expression and it's a LOT less typing.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 20, 2010 at 8:20 pm
It's really a personal preference as AFAIK there's no performance difference, hence the "I prefer to avoid the short version". The longer (searched, or boolean depending on the documentation) version allows the ability to use multiple expressions (which I tend to have to do on a regular basis) and inequality operators rather than just a list of values with equality operators. Also because of this I can be more consistent and I don't have to remember more than one syntax.
But again, it's personal preference, so use what works for you.
-Luke.
September 20, 2010 at 8:54 pm
I don't like the short version at all because it "lies". If you expand the code it actually executes against variables, you find a big surprise. And if you want to do something "tricky", you're in for an even bigger surprise because of the way it actually works in the background...
For example, you would think that the following would never produce a value of "Unknown" but... guess again...
SELECT TOP (1000)
CASE ABS(CHECKSUM(NEWID()))%3 --Produces 0 through 2
WHEN 0 THEN 'Zero'
WHEN 1 THEN 'One'
WHEN 2 THEN 'Two'
ELSE 'Unknown'
END
FROM Master.sys.All_Columns
Here's the reason why... the scalar operator is actually transformed and the code that actually gets executed looks like the following...
Scalar Operator(CASE
WHEN abs(checksum(newid()))%(3)=(0)
THEN 'Zero'
ELSE CASE
WHEN abs(checksum(newid()))%(3)=(1)
THEN 'One'
ELSE CASE
WHEN abs(checksum(newid()))%(3)=(2)
THEN 'Two'
ELSE 'Unknown'
END
END
END)
And that's why the "Unknown" value sometimes happens. The first random generator might not be 0 (could be 1 or 2), the second might not be 1 (could be 0 or 2), and the third might not be 2 (could be 0 or 1). That means that it falls all the way through to the "Unknown" ELSE even though no value was ever produced that wasn't a 0, 1, or 2. 😛
Try the same thing with a variable and look at the properties of the Scalar Operator... same thing but you never notice because the WHEN value doesn't change.
That's why I don't like the shorthand method of CASE.... what you see is NOT what you get. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
September 21, 2010 at 6:02 am
Jeff Moden (9/20/2010)
I don't like the short version at all because it "lies". If you expand the code it actually executes against variables, you find a big surprise. And if you want to do something "tricky", you're in for an even bigger surprise because of the way it actually works in the background...[sic]
That's why I don't like the shorthand method of CASE.... what you see is NOT what you get. 😉
Apparently there is a performance/code related reason for why I do what I do. Thanks for the correction and the sample code Jeff. Good to start the day learning something new.
-Luke.
September 21, 2010 at 6:25 am
Jeff Moden (9/20/2010)
I don't like the short version at all because it "lies".
So a Simple CASE, which on the face of it should work quicker than a Searched CASE because the expression could be evaluated only once, in fact evaluates the expression for each condition. Well I'll be darned. It works the same as a Searched CASE.
Thanks Jeff, there's another myth busted 😎
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 21, 2010 at 7:13 am
Jeff Moden (9/20/2010)
I don't like the short version at all because it "lies".
That's very interesting, but, realistically, how often do people use nondeterministic functions like NEWID() in the comparison expression of a CASE statement?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 21, 2010 at 7:25 am
drew.allen (9/21/2010)
Jeff Moden (9/20/2010)
I don't like the short version at all because it "lies".That's very interesting, but, realistically, how often do people use nondeterministic functions like NEWID() in the comparison expression of a CASE statement?
Drew
I was thinking the same Drew, but what Jeff points out is that Simple and Searched CASE both operate by evaluating the expression for each condition until it "meets true". I've always distinguished between the two types and used Simple CASE where possible on the misunderstanding that it evaluates the condition only once and hence has to be faster. I can feel a test coming on...
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 21, 2010 at 10:03 am
I don't see how that's a disadvantage of the simple CASE. The complex CASE will have the same issue for the same reason. The first version is still much easier to code and vastly easier to change.
Scott Pletcher, SQL Server MVP 2008-2010
September 21, 2010 at 10:12 am
scott.pletcher (9/21/2010)
I don't see how that's a disadvantage of the simple CASE. The complex CASE will have the same issue for the same reason. The first version is still much easier to code and vastly easier to change.
It's a disadvantage of Simple CASE because if you are expecting the expression to be evaluated only once, which you'd assume from looking at the code, then you'd be wrong.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply