June 11, 2009 at 11:07 pm
scope of my work is
my select statement is having null value in a column as shown below,
1first
2NULL
32nd
4NULL
5NULL
6NULL
what i want if there is any null value found previous column will be placed to current row... the result of my query should be like this
1first
2first
32nd
42nd
52nd
62nd
what i was thinking to use a udf function where I can place value in Context_Info, if there is value found in row calling the function and set this value in Context_Info otherwise null value found set the column data from Context_Info.
Unfortunately, udf can't change the state out of the world...
help out plz..
June 11, 2009 at 11:18 pm
naumanikram2008 (6/11/2009)
scope of my work ismy select statement is having null value in a column as shown below,
1first
2NULL
32nd
4NULL
5NULL
6NULL
what i want if there is any null value found previous column will be placed to current row... the result of my query should be like this
1first
2first
32nd
42nd
52nd
62nd
what i was thinking to use a udf function where I can place value in Context_Info, if there is value found in row calling the function and set this value in Context_Info otherwise null value found set the column data from Context_Info.
Unfortunately, udf can't change the state out of the world...
help out plz..
You assume a processing order in your resultSET. You can solve the above problem in different ways, but since the number of consequent NULL values in your result may not be limited, writing the result into a temporary table and then updating it with the help of a cursor would be a solution.
Example:
CREATE TABLE #tab (id int, data varchar(100))
GO
INSERT INTO #tab (id, data)
SELECT 1, 'first' UNION ALL
SELECT 2, NULL UNION ALL
SELECT 3, '2nd' UNION ALL
SELECT 4, NULL UNION ALL
SELECT 5, NULL UNION ALL
SELECT 6, NULL
GO
DECLARE @id int
DECLARE @data varchar(100)
DECLARE @oldData varchar(100)
DECLARE c CURSOR FOR
SELECT id, data
FROM #tab
ORDER BY id
OPEN c;
FETCH NEXT FROM c into @id, @data
SET @oldData = @data
WHILE (@@FETCH_STATUS -1)
BEGIN
IF (@data IS NULL) BEGIN
UPDATE #tab SET data=@oldData WHERE id=@id
END
FETCH NEXT FROM c into @id, @data
IF(@data IS NOT NULL) BEGIN
SET @oldData = @data
END
END
GO
CLOSE c;
GO
DEALLOCATE c;
select * from #tab
Regards,
Andras
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply