Where clause - Where "string" in any field in specified table
-
I have written a large complex stored procedure which produces a dataset of 39 items or so per record. These items come from joins to various tables and sub queries but I have written it so the vast majority outputs are Y/N. If there is a 'Y' in 1 or more of 25 of the fields I want to assign a level, let's call it Red. If there is a Y in any of the fields per record I want to assign a different level to the record, let's call it yellow. If no Y's are found then assign Green
I could have a huge Where statement where I search in the form 'ColA = Y OR ColB = Y OR ColC...' but this seems pretty inefficient and labour intensive.
Therefore: Can anyone think of a way to search for a simple consistent string in any field on a record so i can assign that record to a level if it's found?
Best I have found is the link below but I was hoping in the the 3 1/2yrs since the last post on this topic there may have been some progress, or at least some new people with new ideas?
http://www.sqlservercentral.com/Forums/Topic750935-338-1.aspx
I'd really appreciate any thoughts you may have on this problem,
Thanks
Steve
-
There is not really a terribly efficient way to do this. You are going to have to type in all the columns. something like this might help make it somewhat easier.
select case when CHARINDEX('y', col1 + col2 + col3 + col4) > 0 then 'Red' else 'Green' end_______________________________________________________________
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/ -
Thanks Sean,
This method did work. It just involved 39 'col1 + col2 +' arguements which may not be terribly efficient but it works and at the end of the day that's al that matters.
I really appreciate it.
Steve
-
Actually for the query the performance shouldn't be too bad for that kind of thing. Coding it can kind of PITA though.
Glad that worked for you and thanks for letting me know.
_______________________________________________________________
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/ -
Is there some reason you can't make Ys = 1 and Ns = 0, then just add them up to avoid the CHARINDEX?
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply