Vipar - 1 year ago 62
SQL Question

# Check to see whether a range of columns hold the same value in a single row?

I got a legacy table with 13 columns that holds values 1-5. They have to be migrated over to a new database. However some of the columns hold the value 9 which is a dummy value for "Can't rate this" and we decided that all "can't rate this" values should be turned into 3 rather than 9.

However some of the rows contain only 9's in all 13 columns and we want to skip those entirely as they are meaningless in the grand scheme of things.

I could hardcode a long "WHERE (Q1 = 9 AND Q2 = 9 AND....) but I figure there have to be an easier solution. The columns have the names Q1, Q2...Q13.

Any help with this?

The simplest solution would be to hard-code `NOT (Q1 = 9 AND Q2 = 9 AND Q3 = 9 AND Q4 = 9 AND Q5 = 9 AND Q6 = 9 AND Q7 = 9 AND Q8 = 9 AND Q9 = 9 AND Q10 = 9 AND Q11 = 9 AND Q12 = 9 AND Q13 = 9)` which one can do in a few seconds using any spreadsheet application.

Slightly faster might be an equivalent `(Q1 < 9 OR Q2 < 9 OR Q3 < 9 OR Q4 < 9 OR Q5 < 9 OR Q6 < 9 OR Q7 < 9 OR Q8 < 9 OR Q9 < 9 OR Q10 < 9 OR Q11 < 9 OR Q12 < 9 OR Q13 < 9)` expression generated in the same spreadsheet.

With possible values limited to [1-5, 9] only, a shorter though less efficient expression can be `Q1+Q2+Q3+Q4+Q5+Q6+Q7+Q8+Q9+Q10+Q11+Q12+Q13 < 117`

Just for the record, the spreadsheet formulas used:

``````    A               B                       C                   D
1   1       ="Q"&A1&" = 9 AND "     ="Q"&A1&"<9 OR "        ="Q"&A1&"+"
2   2       ="Q"&A2&" = 9 AND "     ="Q"&A2&"<9 OR "        ="Q"&A2&"+"
...
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download