Check null values for more than one column

[rsrinivasan]

Hi all,

I have one table with 24 fields in SQL server.
In this some of the fields has NULL value.
But i want to get the records which does not have any NULL value in any one of the fields.
This is my Query:

select * from tablename
where  v1  is not null and v2  is not null and  
v3  is not null and v4  is not null and  
v5  is not null and v6  is not null and
v7  is not null and v8  is not null and
v9  is not null and v10 is not null and
v11 is not null and v12 is not null and
v13 is not null and v14 is not null and
v15 is not null and v16 is not null and
v17 is not null and v18 is not null and
v19 is not null and v20 is not null and
v21 is not null and v22 is not null and
v23 is not null and v24 is not null

Is it correct? Or can i use some other method. Because i have millions of records in this table.

[zachster17]

I think that's the only way; below is a variation I actually to check records with no null values using ISNULL.

SELECT * FROM tablename
WHERE ISNULL(v1 + v2 + v3 + v4 + v5 + v6 + ... + v24,'n') <> 'n'

If anything inside is null it treats the whole thing as null.

where n are the fields you want to check for null values
All the fields I check are varchar fields, but I think you would have to use cast or ocnvert for nonvarchar fields (if you use 'n' as the null value)

Thanks,

Zach

[ck9663]

That would depend on the setting of SET CONCAT_NULL_YIE LDS_NULL. For more details read this

-- CK

[r035198x]

If you are using some front-end language you could shorten the code by creating that query using a loop.

[loora]

i have the same issue
would u please explained to me the deatil expression of isNull function in oracle or if any one has other way to create query to check null values for more than one column where the both of the column has number value.
i faced error when i write the where statement as follow:
select column1, column2
from table
where coulmn1 and coulmn2 is not null;