COALESCE to replace multiple CASE statements

Question

COALESCE to replace multiple CASE statements

nimalatissa

SSC Eights!

Points: 881
More actions
May 27, 2014 at 1:59 pm

#289303

Hello Guys,
I have a query with huge number of case statements. Basically I need to short this query with getting rid of these hundreds of CASE statements.
because of the nature of the application I am not allowed to use a function, and just wondering if there is a possible way to rewrite this with COALESCE(). any help much appreciated.
SELECT
CASE WHEN A.[COL_1] LIKE '%cricket%' THEN 'ck' + ',' ELSE '' END +
CASE WHEN A.[COL_1] LIKE '%soccer%' THEN 'sc' + ',' ELSE '' END +
....
CASE WHEN A.[RESIUTIL_DESC] LIKE '%base%ball' THEN 'BB' + ',' ELSE '' END
FROM TableName A

Viewing 15 posts - 1 through 15 (of 22 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

I can't see why would you want to use COALESCE as you're not evaluating nulls.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

nimalatissa SSC Eights! Points: 881 More actions · Answer 2

Thanks for the reply. it looks to me there is no point in trying to squeeze COALESCE here. Do you see any other way to rewrite this in much shorter way ?

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

nimalatissa (5/27/2014)
Thanks for the reply. it looks to me there is no point in trying to squeeze COALESCE here. Do you see any other way to rewrite this in much shorter way ?

Maybe have a lookup table with an order of preference? You are going to get pretty poor performance on this no matter what you do because of the leading wildcard in your condition though.

_______________________________________________________________

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/

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

I agree, a redesign to normalize the tables should help you solve this and other issues.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 5

Luis Cazares (5/27/2014)
I agree, a redesign to normalize the tables should help you solve this and other issues.

Excuse me Luis, but where did you get the idea that a redesign to normalize the tables would be helpful? Did I miss the post that discuss the table structure and normalization?

Please don't make this more confusing by referring to something you have no knowledge of. The tables could be perfectly normalized. We have no way of knowing based on the information posted here.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

nimalatissa SSC Eights! Points: 881 More actions · Answer 6

I'm very confused. These tables are normalized to 3rd normal form. There shouldn't be anything to do with that. the column in this table have comma separated values and I need to return a specif value for those comma separated values. This was working fine, but we are seeing slowness and trying get rid of these CASE statements.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 7

Alvin,

The code makes clear that there's a concatenation based on multiple values on a single column.

CASE WHEN A.[COL_1] LIKE '%cricket%' THEN 'ck' + ',' ELSE '' END +

CASE WHEN A.[COL_1] LIKE '%soccer%' THEN 'sc' + ',' ELSE '' END +

....

That means that the table is not properly normalized. I'm not sure why nimalatissa would say that they are normalized and at the same time there's a column with comma separated values.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

This is just an idea that might work and a shot in the dark at most. Maybe a nested replace will perform better.

Something like this:

DECLARE @CSV varchar(100) = 'cricket, soccer, baseball, golf'

SELECT REPLACE(REPLACE(REPLACE(@CSV,'cricket','ck'),'soccer','sc'),'baseball','bb')

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

nimalatissa SSC Eights! Points: 881 More actions · Answer 9

Sorry if this evermore confusing. This particular Table(DECODED Table) has Decoded values(Descriptions) and there Codes are stored in another table(FLAT Tables) . Both these tables has Look up tables so application can read and store in DECODED and FLAT Tables. Both Decoded and FLAT Tables has proper Primary Keys and Foreign keys.

The Particular query I posted here is called by third party RETS((Real estate Transaction Standard) Clients.

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

nimalatissa (5/27/2014)
I'm very confused. These tables are normalized to 3rd normal form. There shouldn't be anything to do with that. the column in this table have comma separated values and I need to return a specif value for those comma separated values. This was working fine, but we are seeing slowness and trying get rid of these CASE statements.

A few general examples of the data in this column would go a long way.

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

nimalatissa (5/27/2014)
Basically I need to short this query with getting rid of these hundreds of CASE statements.

WHY? Have you proven that they are either a performance or resource usage problem? If not, leave them alone.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 12

Luis Cazares (5/27/2014)
Alvin,
The code makes clear that there's a concatenation based on multiple values on a single column.
CASE WHEN A.[COL_1] LIKE '%cricket%' THEN 'ck' + ',' ELSE '' END +
CASE WHEN A.[COL_1] LIKE '%soccer%' THEN 'sc' + ',' ELSE '' END +
....
That means that the table is not properly normalized. I'm not sure why nimalatissa would say that they are normalized and at the same time there's a column with comma separated values.

Luis, the code says NOTHING about the tables! Stop making assumptions!!

The comma separated values are the output of the query. Did you ever consider they might not be inserted into a table?

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

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

Alvin Ramard (5/28/2014)
Luis Cazares (5/27/2014)
Alvin,
The code makes clear that there's a concatenation based on multiple values on a single column.
CASE WHEN A.[COL_1] LIKE '%cricket%' THEN 'ck' + ',' ELSE '' END +
CASE WHEN A.[COL_1] LIKE '%soccer%' THEN 'sc' + ',' ELSE '' END +
....
That means that the table is not properly normalized. I'm not sure why nimalatissa would say that they are normalized and at the same time there's a column with comma separated values.
Luis, the code says NOTHING about the tables! Stop making assumptions!!
The comma separated values are the output of the query. Did you ever consider they might not be inserted into a table?

nimalatissa (5/27/2014)
...the column in this table have comma separated values...

Alvin, quit giving Luis a hard time!

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

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 14

ChrisM@Work (5/28/2014)
Alvin Ramard (5/28/2014)
Luis Cazares (5/27/2014)
Alvin,
The code makes clear that there's a concatenation based on multiple values on a single column.
CASE WHEN A.[COL_1] LIKE '%cricket%' THEN 'ck' + ',' ELSE '' END +
CASE WHEN A.[COL_1] LIKE '%soccer%' THEN 'sc' + ',' ELSE '' END +
....
That means that the table is not properly normalized. I'm not sure why nimalatissa would say that they are normalized and at the same time there's a column with comma separated values.
Luis, the code says NOTHING about the tables! Stop making assumptions!!
The comma separated values are the output of the query. Did you ever consider they might not be inserted into a table?
nimalatissa (5/27/2014)
...the column in this table have comma separated values...
Alvin, quit giving Luis a hard time!

Sorry Chris, but Luis is doing nothing but making this more confusing for the OP. There's nothing to back up his assumptions and the suggestion he gave is WRONG!

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]