I have a view with a large number of columns - about 120. The view is performing error checking against a table, and each column is another error condition it is checking for. The possible values are:
NULL (no error)
"Warning: <blah blah>" for warnings
"<blah blah>" for errors
I would like to create a summary view on this view that counts up the number of columns in each status, NOT INCLUDING the first few columns that give general information on the record (such as its ID and file location).
I could write it with a whole lot of case statements, along the lines of:
case CheckField1 when null then 0 else 1 end + case CheckField2 when null then 0 else 1 end (etc) and come up with the total number of errors.... but as soon as I add another field to the original view I'd have to come back and fix this. And that doesn't
account for errors versus warnings, so I'd have to do the whole huge case + case statement twice, once looking for not-null and once looking for "warning%"
ETA: All of the columns I am interested in are named "check_..." and have the same varchar(1000) data type, if that helps. The other fields that I do not want to count are named and sized diferently.
There's got to be a better way.... and I thought you guys might be able to help point me in the right direction.
View Complete Post