October 11, 2002 at 9:11 am
I want to return rows from a table that only contain a full set of data i.e. where there are no NULL columns anywher in the row.
from this dataset
id col1 col2 .. coln
1 a a .. a
2 a a .. a
3 NULL a .. a
4 a a .. a
5 a a .. NULL
I only want to return rows 1,2 and 4
For a small number of columns this would be easy enough to achieve using ISNULL but the table could have anywhere between 50 - 100 columns and I dont want to hard code 50 - 100 isnulls as it looks like it will be likely to contain a varying number of columns so dont think I'd be able to achieve it anyway
Thanks if you can help
October 11, 2002 at 9:34 am
You'd have to include in the where clause
where cola is not null
and colb is not null
...
Steve Jones
October 11, 2002 at 9:43 am
Thanks for your post steve.
I could do that but dont want to as I'd have to include a 'is not null' statement 50 - 100 times
Ideally I want to create these tables with a varying amount of columns and each column will have its own usefull name not just cola, colb .. coln for example otherwise it would be slightly easier to cut and paste code up to 100 times. I could have 50 versions of the code testing from 50 to 100 columns but unfortunately the column names will be different
Regards
Mike!
October 11, 2002 at 11:40 am
I am afraid doing is null will not do exactly what you stated since the column will still be in the output. I don't have an example as I don't have a server to connect to here right off. But you will need to build a dynamic sql statement. My thoughts are
Create a cursor object to get the column names from the information_schema.columns view and loop thru them.
Have it check for the existance of a null in a column. This will have to be dynamic as well, something like
SET @sql = 'SELECT COUNT(CASE WHEN ' + @colname + ' IS NULL THEN 1 ELSE 0 END) AS CNT FROM ' + @tblName
and use sp_executesql with an output parameter to get a return of the count. If > 0 there are NULLs other column is full.
If full then store that column into a variable with a , in the data. After the cursor completes the variable will contain only columns with non-nulls and you can do like this.
SET @sql = 'SELECT ' + LEFT(@collist, LEN(@collist) -1) + ' FROM ' + @tblName
The left is to remove the extra , at the end.
the EXEC(@sql) and you will have what you asked. Now the object that wil display will need to be smart enough to know the number of columns and adjust for each run.
But this is the only way I see to get you exact results.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
October 29, 2002 at 10:27 am
>> I want to return rows from a table that only contain a full set of data i.e. where there are no NULL columns anywhere in the row. <<
You talk about having 50 to 100 columsn in atable, almost all fo which are NULL-able. This means your database design is really screwed up. That is more NULLs in one table than exist in the entire payroll package for General Motors.
Since you did not post any DDL, we have no spec to work from. But you need to re-do this schema -- screwed up designs lead to screwed up queries.
October 30, 2002 at 4:32 am
I like Antares solution, its really the way to go if you're going to stick with the variable column design. Agree with Joe that it's not good design, now is the time to change it if you can - not always possible depending on where you're at in the life cycle.
Joe, shouldnt all payroll packages have about the same amount of nulls? Just different numbers of employees?
Andy
October 31, 2002 at 2:04 am
maybe something like
where not coalesce(col1, col2, col3, ....) is null
SuperSKa
October 31, 2002 at 5:04 am
I think u add one more column in your table with name like row_totalnull with initial value 0.At the time of insertion and updation of any col, just check the value of that column.If it is null than increment value of row_totalnull by 1.if null values change to some data,than decrement it by 1.In your query u just check the value of row_totalnull.if it is 0 ,that means rows dont have any null.
October 31, 2002 at 7:36 am
Probably overkill, but one straight forward way to get your result set is simply to create another copy of your table that doesn't allow null, copy all the data and select the results. eg:
create table #1 (pk int identity(1,1),c1 int, c2 int, c3 int)
insert into #1 (c1,c2,c3) values(1,null,null)
insert into #1 (c1,c2,c3) values(1,2,null)
insert into #1 (c1,c2,c3) values(1,2,3)
insert into #1 (c1,c2,c3) values(10,20,30)
set xact_abort off
create table #2 (pk int, c1 int not null, c2 int not null, c3 int not null)
declare @pk int
set @pk = 1
while @pk <=(select max(pk) from #1)
begin
insert into #2
select * from #1
where pk = @pk
end
select * from #1
select * from #2
drop table #1
drop table #2
set xact_abort on
November 1, 2002 at 12:51 am
Hi,
Can you try like this
Select * from <table Name> where col1+col2+col3... is not null
Maheshwar
Maheshwar
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply