DELETE if certain # of columns are blank? 1

[link9]

What is the SQL solution for checking for a certain number of blank columns?

In our database, we store survey responses, and let's say that in order for a survey to be considered valid, the respondent has to answer 28 questions.

How would I ask the question to the database to find out if at least 28 columns on any given row are not null? I currently have something worked out w/ programming in the upload procedure, but I get the sneaking suspicion that there's a better way. Isn't there always? ;-)

Thanks!
Paul Prewett

 

[tlbroadbent]

You could do something like this...

Select *
From Table
where
(Case When Ans1 Is Null Then 0 Else 1 End +
Case When Ans2 Is Null Then 0 Else 1 End +
Case When Ans3 Is Null Then 0 Else 1 End +
.
.
.
Case When Ans38 Is Null Then 0 Else 1 End +
Case When Ans39 Is Null Then 0 Else 1 End +
Case When Ans40 Is Null Then 0 Else 1 End) > 28 Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[link9]

Thanks, Terry. Always keeping me on the straight and narrow.