Want to use Context_Info in UDF user defined function.

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

  • naumanikram2008 (6/11/2009)


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

    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


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply