cursor to find earliest non-null entry for every column in table is not working (SQL Server 2008)

Hi,

I have a small script with which I am using a cursor to return the earliest non-null entry for every column in a table (200+ columns).  The cursor is iterating through each column value fine, but the minFileSet (which I can use to tie back to a date) is coming back incorrect (it looks to be correct for the first column, then this value is applied to all other columns in the results).

Here is the cursor:

DECLARE @columnName nvarchar(255)
DECLARE @minFileSet nvarchar(255)


DECLARE columnName CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTable';
OPEN ColumnName
FETCH ColumnName into @columnName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @minFileSet = (SELECT MIN(fileSet) FROM myTable WHERE @columnName IS NOT NULL)
    SELECT @minFileSet, @columnName

    FETCH NEXT FROM ColumnName into @columnName
END

CLOSE ColumnName
DEALLOCATE ColumnName

The results indicate that @columnName is being iterated correctly, but @minFileSet returns a correct value for only the first iteration.  All others contain this same value:

1)   1     column1name
2)   1     column2name
3)   1     column3name
....
..


Is there something fundamentally wrong with this?  Can @columnName not be passed in to dynamically create the WHERE clause within my SET statement?

Thanks
LVL 1
by6738Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:

You only want the min(FILESET) from the non null columns.
Is fileset the autonumber column?
   
try using the top(1) and order by
0
by6738Author Commented:
fileSet is not an autonumber column, rather just an ID that allows me to track back to a date that the row was received from our source system.  

I just tried replacing my:

SET @minFileSet = (SELECT MIN(fileSet) FROM myTable WHERE @columnName IS NOT NULL)

with

SET @minFileSet = (SELECT top 1 fileSet FROM myTable WHERE @columnName IS NOT NULL ORDER BY fileSet)

and received the same results.  There are definately columns in the table that are null for every record, and for these I would expect to receive a null result for my query.  Instead, I am receiving the fileSet for the first column and it appears to be replicated for every iteration.
0
arnoldCommented:
You would get the same results since min(fileset) and order by fileset (implicit asc ) will always return the same value.

If you want to get a date based response, you should use the date field to sort or use a different parameter.

How is the ID fileset relate to the inserted row/column?
i.e. you have four IDs fileset 1,2,3,4 no matter what column you look at , the MIN(fileset) for a column that is not null will always be 1 unless there is no record with fileset=1 and column not being null.

what is the data in the row
fileset column1 column2 column3 column4 ... etc.

is your setup such that each row only has one non-null column?
In short if there is a row that has fileset=1 and all columns are not null this is the record that will always be displayed.
if fileset ID a primary key i.e. no duplicates?
how the date column relates to the fileset ID column?




0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

by6738Author Commented:
To answer your questions -

- There are a subset of the columns within the table that are NULL for every record, and for these I would expect to get a NULL result for my query, yet I receive the result of the first iteration of the cursor.

- Each row can have multiple columns with NULL values.

It may be easier to describe the issue with a larger example:

Here's my table:

fileSet   colA   colB      colC
1            abc    NULL    NULL
2            def     345      NULL
3            ghi      NULL   NULL
4            qwe   NULL   456

When I run my cursor, I would expect to see the following results:

fileset     column
1             colA
2             colB
4             colC

Instead, I am getting:

fileset   column
1           colA
1           colB
1           colC          
0
arnoldCommented:
is fileset a unique field
i.e. using your example data:
fileSet   colA   colB      colC
1            abc    NULL    NULL
2            def     345      NULL
3            ghi      NULL   NULL
4            qwe   NULL   456
1             sfd   325    124

If you run the query manually
select min(FILESET) from table where colc is not null do you get a different result that provided by the cursor?
0
LowfatspreadCommented:
DECLARE @columnName nvarchar(255)
DECLARE @minFileSet nvarchar(255)
declare @sql varchar(4000)


DECLARE columnName CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTable';
OPEN ColumnName
FETCH ColumnName into @columnName
order by ordinal_position

WHILE @@FETCH_STATUS = 0
BEGIN
   Set @sql='SET @minFileSet = (SELECT MIN(fileSet) FROM myTable WHERE ' + @columnName +' IS NOT NULL)'
    Print @sql
    Exec (@sql)
    SELECT @minFileSet, @columnName

    FETCH NEXT FROM ColumnName into @columnName
END

CLOSE ColumnName
DEALLOCATE ColumnName

/*
 you need to use dynamic sql
  otherwise the value in @columnname is always not null
  and you aren't testing for the value in the column being null
*/

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.