Nested CASE statement with in operator

Question

Post reply

Nested CASE statement with in operator

gregtlaughlin

SSC Veteran

Points: 229
More actions
March 20, 2012 at 12:07 pm

#258094

Select PeriodKey, Sum(dbo_nelnet.LNLCUP) AS Balance,
Case when BRQSTA in('MA','RI','CT','VT','NH') then 'New England'
else case When BRQSTA in('NJ','NY','PA','DE') then 'Mid Atlantic'
else case When BRQSTA in('MI','IL','OH') then 'Midwest'
else 'OOFP'
end end end as Region
FROM dbo_NELNET INNER JOIN dbo_NELNET_BR ON dbo_NELNET.ID = dbo_NELNET_BR.ID
where periodkey = 1359
group by periodkey,Case when BRQSTA in('MA','RI','CT','VT','NH') then 'New England'
else case When BRQSTA in('NJ','NY','PA','DE') then 'Mid Atlantic'
else case When BRQSTA in('MI','IL','OH') then 'Midwest'
else 'OOFP'
end end end as Region
The code works up until the very last line (14 - end end end as Region in the group by statement) when the following error pops up:
Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'as'.
What am I missing? Any assistance would be greatly appreciated. Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

You can't give your group by derived column an alias. Remove the "as Region" and you should be fine.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Andrew Diniz SSCommitted Points: 1852 More actions · Answer 2

As the predicate involves the same field there's no need for nested CASE statements; a single CASE statement will suffice. Moreover, it improves readability:

SELECT

PeriodKey,

SUM(dbo_nelnet.LNLCUP) AS Balance,

CASE WHEN BRQSTA IN('MA','RI','CT','VT','NH') THEN 'New England'

WHEN BRQSTA IN('NJ','NY','PA','DE') THEN 'Mid Atlantic'

WHEN BRQSTA IN('MI','IL','OH') THEN 'Midwest'

ELSE 'OOFP'

END AS Region

FROM dbo_NELNET

INNER JOIN dbo_NELNET_BR ON dbo_NELNET.ID = dbo_NELNET_BR.ID

WHERE PeriodKey = 1359

GROUP BY

PeriodKey,

CASE WHEN BRQSTA IN('MA','RI','CT','VT','NH') THEN 'New England'

WHEN BRQSTA IN('NJ','NY','PA','DE') THEN 'Mid Atlantic'

WHEN BRQSTA IN('MI','IL','OH') THEN 'Midwest'

ELSE 'OOFP'

END;

This won't noticably affect performance as the optimser performs this simplification anyway; it just makes the code more legible.

If performance is a problem and applying indexes is an option, consider putting your BRQSTA codes and their descriptions in a table and perform an outer join to it. You can replace unmatched rows with 'OOFP' using COALESCE or ISNULL.

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

What version of SQL Server are you using?

^{“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