Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Where clause - Where "string" in any field in specified table Expand / Collapse
Author
Message
Posted Thursday, September 19, 2013 10:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 3:07 AM
Points: 4, Visits: 16
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
Post #1496482
Posted Thursday, September 19, 2013 10:27 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:21 PM
Points: 13,083, Visits: 11,918
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1496488
Posted Monday, September 23, 2013 4:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 3:07 AM
Points: 4, Visits: 16
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
Post #1497316
Posted Monday, September 23, 2013 7:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:21 PM
Points: 13,083, Visits: 11,918
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1497380
Posted Monday, September 23, 2013 6:57 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:01 PM
Points: 3,609, Visits: 5,222
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1497610
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse