?
Solved

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

Posted on 2010-04-06
8
Medium Priority
?
359 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:by6738
  • 3
  • 2
6 Comments
 
LVL 81

Expert Comment

by:arnold
ID: 29926105

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
 
LVL 1

Author Comment

by:by6738
ID: 29926895
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
 
LVL 81

Expert Comment

by:arnold
ID: 29928121
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
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
LVL 1

Author Comment

by:by6738
ID: 29931066
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
 
LVL 81

Expert Comment

by:arnold
ID: 29933567
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
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 1000 total points
ID: 29945647
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

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question