Why only final row set to variable on CASE select?

Question

Post reply

Why only final row set to variable on CASE select?

neil-560592

SSCommitted

Points: 1557
More actions
July 26, 2012 at 6:39 am

#259805

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_ShortNameCurrencies_ShortName
USDNYCNULL
EURPAREUR
GBPLONGBP
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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 1

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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

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 Shaw}

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

Lokesh Vij SSChampion Points: 10836 More actions · Answer 3

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 questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Karthiart Ten Centuries Points: 1070 More actions · Answer 4

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.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

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 Shaw}

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

Lokesh Vij SSChampion Points: 10836 More actions · Answer 6

+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 questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

neil-560592 SSCommitted Points: 1557 More actions · Answer 7

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