Returning Rows That Dont Contain any NULLS!

  • 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

  • You'd have to include in the where clause

    where cola is not null

    and colb is not null

    ...

    Steve Jones

    sjones@sqlservercentral.com

    http://www.sqlservercentral.com/columnists/sjones

  • 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!

  • 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)

  • >> 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.

  • 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

    http://www.sqlservercentral.com/columnists/awarren/

  • maybe something like

    where not coalesce(col1, col2, col3, ....) is null

    SuperSKa

  • 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.

  • 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

    set @pk = @pk + 1

    end

    select * from #1

    select * from #2

    drop table #1

    drop table #2

    set xact_abort on

  • 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