Why only final row set to variable on CASE select?
-
I have the following query on SQL 2008 which runs two select statements:
DECLARE @result varchar(50)
SET @result = 'Start'
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID('tempdb..#Cities'))
BEGIN
DROP TABLE #Cities
END
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID('tempdb..#Currencies'))
BEGIN
DROP TABLE #Currencies
END
CREATE TABLE #Cities (Cities_Id INT, Cities_ShortName VARCHAR(50))
CREATE TABLE #Currencies (Cities_Id INT, Currencies_ShortName VARCHAR(50))
insert #Cities
select 1, 'LON' --London
insert #Cities
select 2, 'PAR' --Paris
insert #Cities
select 3, 'NYC' --New York
insert #Currencies
select 1, 'GBP'
insert #Currencies
select 2, 'EUR'
SELECT
CASE
WHEN t.Cities_ShortName = 'NYC'
THEN 'USD'
ELSE
c.Currencies_ShortName
END
,t.Cities_ShortName
,c.Currencies_ShortName
FROM #Cities t
LEFT JOIN #Currencies c ON t.Cities_Id = c.Cities_Id
ORDER BY c.Currencies_ShortName --desc
SELECT @result = @result +
CASE
WHEN t.Cities_ShortName = 'NYC'
THEN 'USD'
ELSE
c.Currencies_ShortName
END
FROM #Cities t
LEFT JOIN #Currencies c ON t.Cities_Id = c.Cities_Id
ORDER BY c.Currencies_ShortName --desc
select @result
The first outputs the following three rows (with headers):
(No column name) Cities_ShortName Currencies_ShortName
USD NYC NULL
EUR PAR EUR
GBP LON GBP
The second query is the same as the first, but it sets a variable (@result) to the values from the first column. So you would think this would output:
StartUSDEURGBP
but in fact it outputs:
StartGBP
Why is it missing the first two values (USD and EUR)?
Thanks
http://90.212.51.111 domain
-
SQL will not set multiple values to a variable like that.
It effectively gets set 3 times, once for each row, overwriting the previous.
You need to concatenate the values yourself in the SELECT statement, & set the variable equal to that.
-
neil-560592 (7/26/2012)
...Why is it missing the first two values (USD and EUR)?
Thanks
Well I'm stumped on this one. The CASE expression is a red herring - it's irrelevant. What's significant is the ORDER BY. Here's an expression which works:
SELECT @result = @result + ',' + ISNULL(result,'NULL')FROM (
SELECT TOP (2147483647) result = c.Currencies_ShortName
--CASE
-- WHEN t.Cities_ShortName = 'NYC' THEN 'USD'
-- ELSE ISNULL(c.Currencies_ShortName,'NULL')
--END
FROM #Cities t
LEFT JOIN #Currencies c ON t.Cities_Id = c.Cities_Id
ORDER BY result DESC
) d
--ORDER BY result ASC
ORDER BY result DESC
...however if you change the outer ORDER BY to ASC it fails.
FWIW folks around here tend to use FOR XML PATH(), but this is quite efficient too:
SELECT@result =
ISNULL(MAX(CASE WHEN rn = 1 THEN result END),'') +
ISNULL(MAX(CASE WHEN rn = 2 THEN result END),'') +
ISNULL(MAX(CASE WHEN rn = 3 THEN result END),'') +
ISNULL(MAX(CASE WHEN rn = 4 THEN result END),'')
FROM (
SELECT
rn = ROW_NUMBER() OVER (ORDER BY Currencies_ShortName DESC),
result =
CASE
WHEN t.Cities_ShortName = 'NYC' THEN 'USD'
ELSE ISNULL(c.Currencies_ShortName,'NULL')
END
FROM #Cities t
LEFT JOIN #Currencies c ON t.Cities_Id = c.Cities_Id
) d
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 -
ChrisM@Work (7/26/2012)
neil-560592 (7/26/2012)
...Why is it missing the first two values (USD and EUR)?
Thanks
Well I'm stumped on this one. The CASE expression is a red herring - it's irrelevant. What's significant is the ORDER BY. Here's an expression which works:
SELECT @result = @result + ',' + ISNULL(result,'NULL')FROM (
SELECT TOP (2147483647) result = c.Currencies_ShortName
--CASE
-- WHEN t.Cities_ShortName = 'NYC' THEN 'USD'
-- ELSE ISNULL(c.Currencies_ShortName,'NULL')
--END
FROM #Cities t
LEFT JOIN #Currencies c ON t.Cities_Id = c.Cities_Id
ORDER BY result DESC
) d
--ORDER BY result ASC
ORDER BY result DESC
...however if you change the outer ORDER BY to ASC it fails.
FWIW folks around here tend to use FOR XML PATH(), but this is quite efficient too:
SELECT@result =
ISNULL(MAX(CASE WHEN rn = 1 THEN result END),'') +
ISNULL(MAX(CASE WHEN rn = 2 THEN result END),'') +
ISNULL(MAX(CASE WHEN rn = 3 THEN result END),'') +
ISNULL(MAX(CASE WHEN rn = 4 THEN result END),'')
FROM (
SELECT
rn = ROW_NUMBER() OVER (ORDER BY Currencies_ShortName DESC),
result =
CASE
WHEN t.Cities_ShortName = 'NYC' THEN 'USD'
ELSE ISNULL(c.Currencies_ShortName,'NULL')
END
FROM #Cities t
LEFT JOIN #Currencies c ON t.Cities_Id = c.Cities_Id
) d
Agreed that the second query is efficient, but in this case we need to know how many data values should be concatenated. In that case I would go for XML Path
SELECT(
SELECT '' + CASE
WHEN t.Cities_ShortName = 'NYC'
THEN 'USD'
ELSE
c.Currencies_ShortName
END
FROM #Cities t
LEFT JOIN #Currencies c ON t.Cities_Id = c.Cities_Id
order by c.Currencies_ShortName desc
FOR XML PATH('')
)As Output
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
ChrisM@Work (7/26/2012)
neil-560592 (7/26/2012)
...Why is it missing the first two values (USD and EUR)?
Thanks
Well I'm stumped on this one. The CASE expression is a red herring - it's irrelevant. What's significant is the ORDER BY. Here's an expression which works:
SELECT @result = @result + ',' + ISNULL(result,'NULL')FROM (
SELECT TOP (2147483647) result = c.Currencies_ShortName
--CASE
-- WHEN t.Cities_ShortName = 'NYC' THEN 'USD'
-- ELSE ISNULL(c.Currencies_ShortName,'NULL')
--END
FROM #Cities t
LEFT JOIN #Currencies c ON t.Cities_Id = c.Cities_Id
ORDER BY result DESC
) d
--ORDER BY result ASC
ORDER BY result DESC
...however if you change the outer ORDER BY to ASC it fails.
FWIW folks around here tend to use FOR XML PATH(), but this is quite efficient too:
SELECT@result =
ISNULL(MAX(CASE WHEN rn = 1 THEN result END),'') +
ISNULL(MAX(CASE WHEN rn = 2 THEN result END),'') +
ISNULL(MAX(CASE WHEN rn = 3 THEN result END),'') +
ISNULL(MAX(CASE WHEN rn = 4 THEN result END),'')
FROM (
SELECT
rn = ROW_NUMBER() OVER (ORDER BY Currencies_ShortName DESC),
result =
CASE
WHEN t.Cities_ShortName = 'NYC' THEN 'USD'
ELSE ISNULL(c.Currencies_ShortName,'NULL')
END
FROM #Cities t
LEFT JOIN #Currencies c ON t.Cities_Id = c.Cities_Id
) d
Chris, But here i have a doubt:
Your query gives output as : GBPEURUSD
Original Expected output is : StartUSDEURGBP
Bit Confused about here and i think the values are not matching with ID's in both the tables then how case will work properly in his first post. Please correct me if am wrong..
Regards,
Karthik.
SQL Developer. -
Karthiart (7/26/2012)
ChrisM@Work (7/26/2012)
neil-560592 (7/26/2012)
...Why is it missing the first two values (USD and EUR)?
Thanks
Well I'm stumped on this one. The CASE expression is a red herring - it's irrelevant. What's significant is the ORDER BY. Here's an expression which works:
SELECT @result = @result + ',' + ISNULL(result,'NULL')FROM (
SELECT TOP (2147483647) result = c.Currencies_ShortName
--CASE
-- WHEN t.Cities_ShortName = 'NYC' THEN 'USD'
-- ELSE ISNULL(c.Currencies_ShortName,'NULL')
--END
FROM #Cities t
LEFT JOIN #Currencies c ON t.Cities_Id = c.Cities_Id
ORDER BY result DESC
) d
--ORDER BY result ASC
ORDER BY result DESC
...however if you change the outer ORDER BY to ASC it fails.
FWIW folks around here tend to use FOR XML PATH(), but this is quite efficient too:
SELECT@result =
ISNULL(MAX(CASE WHEN rn = 1 THEN result END),'') +
ISNULL(MAX(CASE WHEN rn = 2 THEN result END),'') +
ISNULL(MAX(CASE WHEN rn = 3 THEN result END),'') +
ISNULL(MAX(CASE WHEN rn = 4 THEN result END),'')
FROM (
SELECT
rn = ROW_NUMBER() OVER (ORDER BY Currencies_ShortName DESC),
result =
CASE
WHEN t.Cities_ShortName = 'NYC' THEN 'USD'
ELSE ISNULL(c.Currencies_ShortName,'NULL')
END
FROM #Cities t
LEFT JOIN #Currencies c ON t.Cities_Id = c.Cities_Id
) d
Chris, But here i have a doubt:
Your query gives output as : GBPEURUSD
Original Expected output is : StartUSDEURGBP
Bit Confused about here and i think the values are not matching with ID's in both the tables then how case will work properly in his first post. Please correct me if am wrong..
Karthik - reasonable points. I think the OP will know what value he initialised the variable @result to. Also, I reckon he will quickly figure out how to reverse the order of the currencies in the string.
Sometimes I deliberately throw in little catches like this - it encourages the OP to think about the solution and to work with it, rather than cookie-cutting it straight into a stored procedure. I'm sure I'm not alone in doing this.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 -
+1
Kartik, even my query gives the same output as that of Chris 🙂
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Thanks for the replies so far.
laurie: SQL does handle multiple value like that - you can see this if you comment out the ORDER BY clause. (you'll get: StartGBPEURUSD )
Chris M and Karthik: I have initialised the @result variable to: Start
(this is at the top of my original query).
I should clarify that I am not so much looking for a solution to the problem, but to understand why the query does not work.
Just to illustrate, I am currently using a workaround which is to load the data into a temp table and running the select on the temp table.
This is in "section 2" of the query:
DECLARE @result varchar(50)
SET @result = 'Start'
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID('tempdb..#Cities'))
BEGIN
DROP TABLE #Cities
END
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID('tempdb..#Currencies'))
BEGIN
DROP TABLE #Currencies
END
CREATE TABLE #Cities (Cities_Id INT, Cities_ShortName VARCHAR(50))
CREATE TABLE #Currencies (Cities_Id INT, Currencies_ShortName VARCHAR(50))
insert #Cities
select 1, 'LON' --London
insert #Cities
select 2, 'PAR' --Paris
insert #Cities
select 3, 'NYC' --New York
insert #Currencies
select 1, 'GBP'
insert #Currencies
select 2, 'EUR'
SELECT
CASE
WHEN t.Cities_ShortName = 'NYC'
THEN 'USD'
ELSE
c.Currencies_ShortName
END
,t.Cities_ShortName
,c.Currencies_ShortName
FROM #Cities t
LEFT JOIN #Currencies c ON t.Cities_Id = c.Cities_Id
ORDER BY c.Currencies_ShortName --desc
SELECT @result = @result +
CASE
WHEN t.Cities_ShortName = 'NYC'
THEN 'USD'
ELSE
c.Currencies_ShortName
END
FROM #Cities t
LEFT JOIN #Currencies c ON t.Cities_Id = c.Cities_Id
ORDER BY c.Currencies_ShortName --desc
select @result
-- Section 2 - use a temp table #CitiesCurrencies
-- and a new variable @result2 also set to 'Start'
DECLARE @result2 varchar(50)
SET @result2 = 'Start'
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID('tempdb..#CitiesCurrencies'))
BEGIN
DROP TABLE #CitiesCurrencies
END
CREATE TABLE #CitiesCurrencies
(Cities_ShortName VARCHAR(50), Currencies_ShortName VARCHAR(50))
INSERT #CitiesCurrencies
SELECT
t.Cities_ShortName
,c.Currencies_ShortName
FROM #Cities t
LEFT JOIN #Currencies c ON t.Cities_Id = c.Cities_Id
ORDER BY c.Currencies_ShortName --desc
SELECT @result2 = @result2 +
CASE
WHEN Cities_ShortName = 'NYC'
THEN 'USD'
ELSE
Currencies_ShortName
END
FROM #CitiesCurrencies
ORDER BY Currencies_ShortName
select @result2
http://90.212.51.111 domain
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply