Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2004
    Posts
    193

    Unanswered: Cursor Process Coding Problem

    Hi guys,

    It's been awhile since I have posted. I have a situation for the group here. I am new to working with cursors. I have a simple one here that I wish to use to update NULL fields in a table called rpt_Scr_B0000_MiniFinancials. I know for a fact that there are NULLs in this table. When I run the select query from the information_schema I get some 60 some odd fields. Anyway, when I run this I get 0 records affected which I know is incorrect. It appears that my cursor is only processing for the first field. I tried changing the @@FETCH_STATUS = 0 to @@FETCH_STATUS > 0 and that didn't work either. What am I doing wrong? Thx.


    DECLARE @FieldName char (25)

    DECLARE cursor_update_rpt_Scr_B0000_MiniFinancials CURSOR For

    select column_name
    from information_schema.columns
    where table_name = 'rpt_Scr_B0000_MiniFinancials'

    open cursor_update_rpt_Scr_B0000_MiniFinancials

    FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
    INTO @FieldName

    update rpt_Scr_B0000_MiniFinancials
    set @FieldName = 0
    where @FieldName is null

    WHILE @@FETCH_STATUS = 0
    BEGIN

    FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
    INTO @FieldName

    END


    CLOSE cursor_update_rpt_Scr_B0000_MiniFinancials
    DEALLOCATE cursor_update_rpt_Scr_B0000_MiniFinancials

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    your update needs to be inside the WHILE Loop

    But aren't you worried about datatypes...and what's wrong with nulls annyway

    (Here we go again)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Posts
    193
    Ok,

    I tried that but now I get all zeros. This is a step forward. I have a table with 66 fields and five records. It appears it is now processing each update but there is still something wrong. Say one of the fields, ie 'abc' has 3 records populated leaving two nulls, the two nulls should be turned into zeros. The issue is that the front end programmer creating the view wants me to populate the nulls with zeros. Is it easier if I use a coalesce function in some way instead?

    Here is the updated code:



    DECLARE @FieldName char (25)

    DECLARE cursor_update_rpt_Scr_B0000_MiniFinancials CURSOR For

    select column_name
    from information_schema.columns
    where table_name = 'rpt_Scr_B0000_MiniFinancials'

    open cursor_update_rpt_Scr_B0000_MiniFinancials

    FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
    INTO @FieldName


    WHILE @@FETCH_STATUS = 0
    BEGIN

    update rpt_Scr_B0000_MiniFinancials
    set @FieldName = 0
    where @FieldName is null

    FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
    INTO @FieldName

    END

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well I would take a different approach...you do know what happens when you assign a 0 to a datetime column don't you. Anyway, cut and paste this code example into query analyzer...it should run no problem.

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(Col1 int, Col2 char(1), Col3 datetime)
    GO
    
    INSERT INTO myTable99(Col1,Col2,Col3)
         SELECT 1   , null, '2006-01-01' UNION ALL
         SELECT null, 'b' , '2006-01-02' UNION ALL
         SELECT 3   , 'c' , null
    GO
    
    SELECT * FROM myTable99
    GO
    
    DECLARE @sql varchar(8000), @collist varchar(8000), @TABLE_NAME sysname
    
    SET @TABLE_NAME = 'myTable99'
    
    SELECT @collist = COALESCE(@collist+', ','') + COLUMN_NAME + ' = '
    	+ 'CASE WHEN ' + COLUMN_NAME + ' IS NULL THEN '
    	+ CASE WHEN DATA_TYPE IN ('char','nchar','varchar','nvarchar','text','ntext') THEN ''''+'0'+'''' ELSE '0' END 
    	+ ' ELSE ' + COLUMN_NAME + ' END'
      FROM INFORMATION_SCHEMA.Columns
     WHERE TABLE_NAME = @TABLE_NAME
    
    SELECT @sql = 'UPDATE ' + @TABLE_NAME + ' SET ' + @collist
    
    SELECT @sql
    
    EXEC(@sql)
    
    SELECT * FROM myTable99
    GO
    
    DROP TABLE myTable99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2004
    Posts
    193
    OK,

    I will take a look at your coding. I should have specified that none of the fields is a datetime. They are all money, int, real, or varchars. Thanks again for your time and diligence.

    Dave

  6. #6
    Join Date
    Feb 2004
    Posts
    193
    We resolved it in-house. Here is the answer! Thanks again.

    ================================================== =====


    DECLARE @FieldName char (25)
    DECLARE cursor_update_rpt_Scr_B0000_MiniFinancials CURSOR For

    select column_name
    from information_schema.columns
    where table_name = 'rpt_Scr_B0000_MiniFinancials'


    open cursor_update_rpt_Scr_B0000_MiniFinancials

    FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
    INTO @FieldName


    WHILE @@FETCH_STATUS = 0
    BEGIN

    execute('
    update rpt_Scr_B0000_MiniFinancials
    set '+@FieldName+' = 0
    where '+@FieldName+' is null
    ')

    FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
    INTO @FieldName

    END


    CLOSE cursor_update_rpt_Scr_B0000_MiniFinancials
    DEALLOCATE cursor_update_rpt_Scr_B0000_MiniFinancials

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well use mine anyway and blow their minds...cursors...ech


    Besides mine will be faster
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    WOuld setting default = 0 on the table structure accomplish what you want.

  9. #9
    Join Date
    Feb 2004
    Posts
    193
    I just tried that, one of the other guys here suggested this also. It fails our process due to the fact that there is an update statement we run to perform calculations, ie averaging. If I try to default the table values to zeros it blows up when it reaches this update with a can't divide by zero error. If the process gets to this update with NULLs it is fine as far not erroring out but these NULL fields don't get populated with zeros either. So the way it is set up I must perform a later update to make all NULLs zero. Make sense?

    Dave

Posting Permissions

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