Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: Replacing NULLS

  1. #1
    Join Date
    Jan 2003
    Posts
    6

    Unanswered: Replacing NULLS

    Table1 Definition
    [timestamp], datetime
    [System201], int
    [System500], int
    [System345], int
    ... (the number of [System###] fields will be different for each facility I deploy this in)

    Current Table1 Data
    [timestamp] [System201] [System500] [System345] ...
    1/1/03 1 2 4
    1/2/02 3 NULL NULL
    1/3/03 NULL 5 8

    I would like to replace the NULL's with the value from the previous record. Is there a quick and dirty way to do this?

    Future Table1 Data
    [timestamp] [System201] [System500] [System345] ...
    1/1/03 1 2 4
    1/2/02 3 2 4
    1/3/03 3 5 8

    Jeffrey

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245

    Re: Replacing NULLS

    I see a cursor in your future (ducks and hides)....

    Seriously, the only way the I know of to transpose one row of data onto another in the type of scenario you describe is to do it with a cursor:

    Code:
    -- Declare Local variables (one for each column, plus an
    -- additional variable for each column to store the previous value)
    DECLARE
    @TimeStamp  datetime,
    @System201 int,
    @System500 int,
    @System345 int,
    ....
    @System201_Old int,
    @System500_Old int,
    @System345_Old int,
    ....
    @Counter int
    
    -- Declare the cursor
    DECLARE rolling_cursor CURSOR
    FROM SELECT * FROM TABLE1 ORDER BY TimeStamp
    
    -- Open the cursor
    OPEN rolling_cursor
    
    -- Set an initial counter
    Set @Counter = 1
    
    -- Fetch the first row of values into the local variables
    FETCH  NEXT FROM rolling_cursor INTO
        @TimeStamp,
        @System201,
        @System500,
        @System345
    
    -- WHILE your way through the table, updating the next row
    -- with the previous rows data when the next row's columns
    -- are null (see COALESCE)
    WHILE fetch_status <> -1
    BEGIN
        IF (@@fetch_status <> -2)
        BEGIN
            IF @Counter = 1 
            BEGIN
                @System201_Old = @System_201
                @System500_Old = @System_500
                @System345_Old = @System_345
            END
            ELSE
            BEGIN
               UPDATE TABLE1 SET 
        @System201 = COALESCE(@System201, @System201_Old),
        @System201 = COALESCE(@System201, @System201_Old),
        @System201 = COALESCE(@System201, @System201_Old),
        ....
               WHERE TimeStamp = @TimeStamp
    
                @System201_Old = @System_201
                @System500_Old = @System_500
                @System345_Old = @System_345
                @Counter = @Counter + 1
            END
            
            FETCH  NEXT FROM rolling_cursor INTO
                @TimeStamp,
                @System201,
                @System500,
                @System345 
    END    
    
    -- Close and deallocate the cursor
    CLOSE rolling_cursor
    DEALLOCATE rolling_cursor
    
    -- Print a message to the user to show how many records were updated
    Print @Counter + ' records updated.'
    Notes:
    1. Not tested
    2. Not guaranteed
    3. Mileage may vary
    4. Please don't sue me
    5. Don't sic the DBA gods on me for suggesting a cursor
    6. I am assuming that the PK on this table is TimeStamp (and that Timestamp is sequential). This solution does not work otherwise.

    Best of luck,

    Hugh Scott
    Originally posted by JeffreyELewis
    Table1 Definition
    [timestamp], datetime
    [System201], int
    [System500], int
    [System345], int
    ... (the number of [System###] fields will be different for each facility I deploy this in)

    Current Table1 Data
    [timestamp] [System201] [System500] [System345] ...
    1/1/03 1 2 4
    1/2/02 3 NULL NULL
    1/3/03 NULL 5 8

    I would like to replace the NULL's with the value from the previous record. Is there a quick and dirty way to do this?

    Future Table1 Data
    [timestamp] [System201] [System500] [System345] ...
    1/1/03 1 2 4
    1/2/02 3 2 4
    1/3/03 3 5 8

    Jeffrey
    Last edited by hmscott; 01-24-03 at 09:18.

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    Jeez, i spend all that time formatting the script with spaces and everything (to show nesting) and it comes out looking out looking like that!?!?!

    First, I humbly apologize if it is difficult to read through. Second, it anyone knows how to make spaces or tabs work on this forum, I would be grateful.

    Regards,

    Hugh Scott

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    keeping my comments on cursors to myself....

    edit your post and put a code at the beggining and a /code at the end like using bold, that should do it.

    Also I think your logic may be flawed, your cursor sorts by time stamp and the examples don't reflect that.
    Paul Young
    (Knowledge is power! Get some!)

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    I appreciate your amendments. You're right on the comment about timestamps -- I missed that in the original statement of requirements.

    Oh well, back to the drawing board!!!

    Regards,

    Hugh Scott

    Originally posted by Paul Young
    keeping my comments on cursors to myself....

    edit your post and put a code at the beggining and a /code at the end like using bold, that should do it.

    Also I think your logic may be flawed, your cursor sorts by time stamp and the examples don't reflect that.

  6. #6
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    1/1/03 1/2/02 1/3/03

    Q1 What PK is on Table1?
    Q2 How do you know the record is previous?

  7. #7
    Join Date
    Dec 2002
    Posts
    1,245
    I assumed that the PK was Timestamp. I inferred that the poster made an error and meant to put /03 instead of /02. I know what happens when you assume...

    Kindest regards,

    Hugh Scott

    Originally posted by ispaleny
    1/1/03 1/2/02 1/3/03

    Q1 What PK is on Table1?
    Q2 How do you know the record is previous?

  8. #8
    Join Date
    Jan 2003
    Posts
    6
    Your right. That was a typo. The year should have been 03 instead of 02 and the PK is the [timestamp] field.

  9. #9
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    it looks like hmscott provided the answer. Post back and let us know if it worked.
    Paul Young
    (Knowledge is power! Get some!)

  10. #10
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Now, when your information is clear, I can post my solution.

    /*
    Script drops Table1, uses transaction to support 2 solutions in one script
    */

    --INIT
    drop table Table1
    GO
    create table Table1(
    [timestamp] datetime primary key clustered
    ,[System201] int null
    ,[System500] int null
    ,[System345] int null
    )
    GO
    set dateformat MDY
    insert Table1( [timestamp],[System201],[System500],[System345] ) values ('1/1/03', 1, 2, 4)
    insert Table1( [timestamp],[System201],[System500],[System345] ) values ('1/2/03', 3,NULL,NULL)
    insert Table1( [timestamp],[System201],[System500],[System345] ) values ('1/3/03',NULL, 5, 8)
    GO

    begin tran
    select * from Table1

    --SOLUTION WITHOUT CURSOR OR PSEDOCURSOR
    /*
    Recomended use of clustered PK on [timestamp] and separate index on each (System...) column for large table
    */
    update t1 set
    t1.[System201]=
    (
    select t2.[System201]
    from Table1 t2
    join
    (
    select [timestamp]=max(t3.[timestamp])
    from Table1 t3
    where (t1.[timestamp]>=t3.[timestamp]) and (t3.[System201] is not null)
    ) x on t2.[timestamp]=x.[timestamp]
    )
    from Table1 t1
    where t1.[System201] is null
    update t1 set
    t1.[System500]=
    (
    select t2.[System500]
    from Table1 t2
    join
    (
    select [timestamp]=max(t3.[timestamp])
    from Table1 t3
    where (t1.[timestamp]>=t3.[timestamp]) and (t3.[System500] is not null)
    ) x on t2.[timestamp]=x.[timestamp]
    )
    from Table1 t1
    where t1.[System500] is null
    update t1 set
    t1.[System345]=
    (
    select t2.[System345]
    from Table1 t2
    join
    (
    select [timestamp]=max(t3.[timestamp])
    from Table1 t3
    where (t1.[timestamp]>=t3.[timestamp]) and (t3.[System345] is not null)
    ) x on t2.[timestamp]=x.[timestamp]
    )
    from Table1 t1
    where t1.[System345] is null

    select * from Table1
    rollback tran
    begin tran
    select * from Table1

    --PSEDOCURSOR SOLUTION
    declare @timestamp datetime
    declare @System201 int
    declare @System500 int
    declare @System345 int
    declare @timestampPre datetime
    declare @System201Pre int
    declare @System500Pre int
    declare @System345Pre int
    set nocount on
    select @timestampPre=[timestamp],@System201Pre=[System201],@System500Pre=[System500],@System345Pre=[System345] from Table1 t1
    where t1.[timestamp]=(select min(t2.[timestamp]) from Table1 t2)
    while 1=1 begin
    select @timestamp=[timestamp],@System201=[System201],@System500=[System500],@System345=[System345] from Table1 t1
    where t1.[timestamp]=(select min(t2.[timestamp]) from Table1 t2 where t2.[timestamp]>@timestampPre)
    if @@rowcount=0 break
    set @System201Pre=isnull(@System201,@System201Pre)
    set @System500Pre=isnull(@System500,@System500Pre)
    set @System345Pre=isnull(@System345,@System345Pre)
    if @System201 is null or @System500 is null or @System345 is null
    update Table1 set
    [System201]=@System201Pre
    ,[System500]=@System500Pre
    ,[System345]=@System345Pre
    where [timestamp]=@timestamp
    set @timestampPre=@timestamp
    end
    set nocount off

    select * from Table1
    rollback tran

  11. #11
    Join Date
    Jan 2003
    Posts
    4
    Forgive me for being simple but there is a quick and dirty way to update previous record with data from next record.

    Select *, identity(int ,1,1) as seq
    into #temp
    from "your table"
    order by timestamp

    update t1
    set t1.System201 = t2.System201
    from #temp t1 inner join #temp t2 on
    t1.seq = t2.seq - 1

  12. #12
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Yes, I call that algorithm "ordered streams". But I was trying to solve
    situation like this

    1 1
    2 NULL
    3 NULL
    ->
    1 1
    2 1
    3 1

    by query without cursor.

  13. #13
    Join Date
    Jan 2003
    Location
    México City
    Posts
    31
    Why not use a subquery?

    Code:
       select  t.fldnam
          from tblnam t
        where t.valfld is not null
            and t.valfld < ( select t1.valfld
                                    from tblnam t1
                                  where t1.keyfld = t.keyfld )
    I'm suppose it isn't the finnest but I'm shure it's cheaper than cursors, no?

  14. #14
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    Cesar, I do not uderstand what you mean by your code.

    It can be solved by single quary

    Code:
    
    select t.keyfld,t.valfld,t1.keyfld,t1.valfld
     from tblnam t
     join tblnam t1 on t1.keyfld=(select max(t2.keyfld) from tblnam t2 where t2.keyfld<=t.keyfld and t2.valfld is not null)
    
    But this query is not scalable (N^3), cursor is better algorithm.

  15. #15
    Join Date
    Jan 2003
    Location
    México City
    Posts
    31

    Joins vs. Subqueries

    I don't know exactly how to calculate the difference between subqueries and joins, focusing in througput, because I used to work only with lower than 100,000 records tables, so my point is I agree about cursor structures and algorithms in theoretical terms, but not in speed, at least in MSSQL7 implementation.
    So I'm always preffer to complicate my scripts with more sophisticated selects/joins/temporary tables/indexes, etc...
    The only drawback I've found is maintenance, because almost any VB programmer can deal with cursors but complex SQL statements.
    So in my record's range its a question of maintenance/performance for me and I've choseen performance until now, against cursors.
    What do you think?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •