Filling In the Gaps

[trenttc]

I want to update a table whenever there is a Null value.
If a column row has a Null value, get the value from first column preceding it, that doesn't have a Null value.

Table1 (before)
q1 q2 q3 q4 ... q50
2 NULL NULL 6 NULL
6 5 NULL NULL NULL
4 NULL 3 NULL 12

Table1 (after)
q1 q2 q3 q4 ... q50
2 2 2 6 6
6 5 5 5 5
4 4 3 3 12

It's tough coding an UPDATE SET stmt for this one.

 

[tlbroadbent]

The following was written for SQL Server rather than generic ANSI SQL. You may be able to modify it for your use.

Select * from table1

Declare @sql varchar(2000), @cid int, @mid int, @tid int
Select @tid=object_id('table1')
Print @tid

Select @cid=1, @mid=max(colid)
From syscolumns
Where id=@tid

While @cid<=@mid
Begin
Select @sql=
'Update table1 Set '+a.name+'='+b.name+
' Where '+a.name+' Is Null And '+b.name+' Is Not Null'
From syscolumns a
Join syscolumns b
On a.ID=b.ID
And a.colid=b.colid+1
Where a.colid=@cid
And a.ID=@tid
Print @sql
Exec(@sql)
Select @cid=@cid+1
End

Select * from table1
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[trenttc]

Terry,
This might work but I can't get the table_id to match.
Select @tid=object_id('table1') gives the value 100334.
I figured out that I can find table1 in syscolumn by specifying 334 as in the following query:
select * from syscolumn
where table_id = 334

Any idea why object_id adds the 100?
What can I do about it?

Thanks

 

[tlbroadbent]

I didn't encounter that problem. Which version of SQL Server are you using? Are you using MS SQL Server or some database system?

In SQL Server 7 and 2000, there is no table_id column in the syscolumns table. Therefore, I suspect tht you are using another database system. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[trenttc]

You are correct. I'm using Sybase Adaptive Server Anywhere. I come to this forum because of the quality and timeliness of the responses from people like yourself.

I actually hardcoded the correct table number, just to get it going, and I get - Column 'name' not found. I think it doesn't like the Update Set a.name=b.name. This has been the difficult part, figuring out the SET stmt.

 

[tlbroadbent]

I'm not familiar with Sybase except that MS SQL Server was originally Sybase. Do a Select * From syscolumns to make sure you have all the correct names. If Sybase uses table_id rather than ID, it probably uses column_name or similar rather than just name. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[trenttc]

The column names (and the table name) are slightly different, and I account for that. I can hardcode the table id to get the thing to execute, it just gives me that - Column 'name' not found error.

I guess the Set stmt is supposed to dynamically change a.name to an actual column name just by finding a Null value, but I don't think that's happening, and I don't know why?

 

[tlbroadbent]

Can you post your script? Maybe I'll be able to spot something. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[trenttc]

Foul-up comments.
Now I get it to come up with a different error message
Correlation name 'a' not found. Hey, this is progress!

 

[trenttc]

I'm using table names (not id) and column numbers.
I stuck the code inside a FOR LOOP.
I also added an ending quote just before Execute (@sql);

BEGIN

DECLARE @sql varchar(2000);
DECLARE @cid int;
DECLARE @mid int;
DECLARE @tid varchar(40);

Select @tid = 'nsm_pricelevels';

Select max(colno) INTO @mid
From syscolumns
Where tname=@tid;

FOR FillTheGaps AS curser CURSOR FOR
SELECT * FROM nsm_pricelevels
DO

Select @cid = 5;

While @cid<=@mid
LOOP
Select @sql=
'Update nsm_pricelevels Set '+a.name+'='+b.name+
' Where '+a.name+' Is Null And '+b.name+' Is Not Null' +
'From syscolumns a
Join syscolumns b
On a.tname=b.tname
And a.colno=b.colno+1
Where a.colno=@cid
And a.tname=@tid';
Execute (@sql);
Select @cid=@cid+1;
END LOOP;

END FOR;

END;

 

[tlbroadbent]

According to the documentation for Sybase Adaptive Server 12.5 there is no tname column in the syscolumns table. The table ID is named ID. Also there is no column named colno. There is a colid column.

There is no need for the cursor and the FOR LOOP. The Update statement handles the table records. The while lop handles the columns as listed in syscolumns.

Try the following.
[tt]
BEGIN

DECLARE @sql varchar(2000);
DECLARE @cid int;
DECLARE @mid int;
DECLARE @tid varchar(40);

Select @tid = 'nsm_pricelevels';

Select max(colno) INTO @mid
From syscolumns c
Join sysobjects o
On c.id=o.id
Where o.name=@tid;

Select @cid = 5;

While @cid<=@mid
LOOP

Select @sql=
'Update nsm_pricelevels Set '+a.name+'='+b.name+
' Where '+a.name+' Is Null And '+b.name+' Is Not Null' +
'From syscolumns a
Join sysobjects o
On a.id=o.id
Join syscolumns b
On a.id=b.id
And a.colid=b.colid+1
Where a.colno=@cid
And o.name=@tid;

Print @sql;

Execute (@sql);

Select @cid=@cid+1;

END LOOP;

END[/tt] Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[angiole]

If you're looking for a one-off hack to build data, the following will give what you need minus the elegance of loops.

Update Tab1 Set Col2 = Col1 Where Col2 null;
commit;

Update Tab1 Set Col3 = Col2 Where Col3 null;
commit;

Update Tab1 Set Col4 = Col3 Where Col4 null;
commit;

... ad infinitum

 

[trenttc]

tlbroadbent is a guru! I had to use systable v sysobjects to get table_id to work. I included the code for those who might need an ASA solution.


BEGIN

DECLARE @sql varchar(2000);
DECLARE @cid int;
DECLARE @mid int;
DECLARE @tid int;

Select table_id INTO @tid
from systable
where table_name = 'nsm_pricelevels';

Select max(column_id) INTO @mid
From syscolumn c
Join systable o
On c.table_id=o.table_id
Where o.table_id=@tid;

Select @cid = 5;

While @cid<=@mid
LOOP

Select @sql=
'Update nsm_pricelevels Set '+a.column_name+'='+b.column_name+
' Where '+a.column_name+' Is Null And '+b.column_name+'Is Not Null'
From syscolumn a
Join systable o
On a.table_id=o.table_id
Join syscolumn b
On a.table_id=b.table_id
And a.column_id=b.column_id+1
Where a.column_id=@cid
And o.table_id=@tid;

Print @sql;

Execute (@sql);

Select @cid=@cid+1;

END LOOP;

END;