May 16, 2014 at 8:31 am
Right now I have to do something like this and it is time consuming every time I have to query a specific table...
SELECT lots_of_columns
FROM table
WHERE (column5 = '1' OR column6 = '1' OR column7 = '1' OR column8 = '1' OR column9 = '1' OR column10 = '1' OR column11 = '1' OR column12 = '1')
AND other_query_critiera_here
Typing out the OR statement gets long, time consuming and prone to errors because that first where line with all the ORs can sometimes have 20+ ORs in it. As some insight, the columns are text columns, sometimes they have data, sometimes they are NULL. Sometimes they have the same data (i.e., column5 and column6 and column12 could both have '1' as values).
May 16, 2014 at 8:39 am
Kevlarmpowered (5/16/2014)
Right now I have to do something like this and it is time consuming every time I have to query a specific table...SELECT lots_of_columns
FROM table
WHERE (column5 = '1' OR column6 = '1' OR column7 = '1' OR column8 = '1' OR column9 = '1' OR column10 = '1' OR column11 = '1' OR column12 = '1')
AND other_query_critiera_here
Typing out the OR statement gets long, time consuming and prone to errors because that first where line with all the ORs can sometimes have 20+ ORs in it. As some insight, the columns are text columns, sometimes they have data, sometimes they are NULL. Sometimes they have the same data (i.e., column5 and column6 and column12 could both have '1' as values).
This should get you started:
SELECT lots_of_columns
FROM table
WHERE '1' IN (column5,column6,column7,column8,column9,column10,column11,column12)
AND other_query_critiera_here
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
May 16, 2014 at 8:41 am
Kevlarmpowered (5/16/2014)
Right now I have to do something like this and it is time consuming every time I have to query a specific table...SELECT lots_of_columns
FROM table
WHERE (column5 = '1' OR column6 = '1' OR column7 = '1' OR column8 = '1' OR column9 = '1' OR column10 = '1' OR column11 = '1' OR column12 = '1')
AND other_query_critiera_here
Typing out the OR statement gets long, time consuming and prone to errors because that first where line with all the ORs can sometimes have 20+ ORs in it. As some insight, the columns are text columns, sometimes they have data, sometimes they are NULL. Sometimes they have the same data (i.e., column5 and column6 and column12 could both have '1' as values).
Are you asking if there is some other way to evaluate a bunch of columns to see if any of them contain the value '1'? There are some other ways that would be possible. They would be far less typing but far worse for performance.
Not really sure what else you can do if you need to return rows where any of a number of columns may contain the information you are looking for. It does seem that if you have to do this type of query repeatedly that something is not quite right. Maybe the data is not normalized? '
And please tell me that when say they are text columns that they are really defined as (n)varchar and not (n)text.
Perhaps if you could post some ddl and sample data along with a sample of what you have to do we can help you find another way.
_______________________________________________________________
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/
May 16, 2014 at 8:43 am
Mind Blown... :w00t:
I obviously jumped to unnecessarily complicated trying to solve my problem without trying the most simplest of solutions first.
May 16, 2014 at 8:51 am
Sean Lange (5/16/2014)
Are you asking if there is some other way to evaluate a bunch of columns to see if any of them contain the value '1'? There are some other ways that would be possible. They would be far less typing but far worse for performance.
Yup... up to 20 columns could have the value I am looking for.
Not really sure what else you can do if you need to return rows where any of a number of columns may contain the information you are looking for. It does seem that if you have to do this type of query repeatedly that something is not quite right. Maybe the data is not normalized? '
Unfortunately... the database is a list of majors/minors/concentrations. The columns are actually...
majr_code_1
majr_code_2
majr_code_1_2
majr_code_2_2
majr_code_minr_1
majr_code_minr_1_2
majr_code_minr_2
majr_code_minr_2_2
majr_code_conc_1
majr_code_conc_1_2
majr_code_conc_1_3
majr_code_conc_2
majr_code_conc_2_2
majr_code_conc_2_3
majr_code_conc_121
majr_code_conc_122
majr_code_conc_123
majr_code_conc_221
majr_code_conc_222
majr_code_conc_223
To get a student associated with a specific program (i.e., Accounting) I would have to check each of those columns to see if the Accounting code exists. A student can major in Accounting, minor in Accounting, have a concentration in Accounting or even double major in Accounting.
And please tell me that when say they are text columns that they are really defined as (n)varchar and not (n)text.
Yes... they are nvarchar, should have stated that before but when I think code, my brain does not think in native SQL code as I am a programmer from another language that is embedding SQL code into it for reporting.
Perhaps if you could post some ddl and sample data along with a sample of what you have to do we can help you find another way.
The columns listed above are just a small subset of some 50 columns in the table where the primary key is their ID and their effective term/semester.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply