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_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

  • 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 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

  • 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

  • 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 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

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

  • 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