Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    176

    Unanswered: Checksum computation help

    Please execute the script below to understand the problem -


    ---
    create table test(id int, col1 int,col2 varchar(5),col3 datetime)
    create table test2(id int, col1 int,col2 varchar(5),col3 datetime)

    --id & col1 make up the PK.

    insert test values(4,4,'d','02/06/2004')
    insert test values(4,4,'e','02/06/2004')

    insert test2 values(4,4,'d','02/06/2004')
    insert test2 values(4,4,'e','02/06/2004')

    select *
    from test

    select *
    from test2

    --The rows are identical.
    --Script A

    select t.*
    from test t
    join test2 t2 on t2.id=t.id
    where CHECKSUM(t.col2,t.col3)<>CHECKSUM(t2.col2,t2.col3)

    --The purpose of the above script is to check for any updates in the two tables. It returns two rows. But as you can see both these rows were present in the table before. So I modify the script to -
    --SCRIPT B
    select t.*
    from test t
    join test2 t2 on t2.col2=t.col2
    where CHECKSUM(t.col3)<>CHECKSUM(t2.col3)

    -- In this case no row is returned.This is exactly what I need. The problem - Now execute the script below.

    TRUNCATE TABLE TEST
    TRUNCATE TABLE TEST2

    insert test values(4,4,'d','02/06/2004')
    insert test values(4,4,'d','02/01/2004')

    insert test2 values(4,4,'d','02/06/2004')
    insert test2 values(4,4,'d','02/01/2004')

    --Now when I execute script B two rows are returned which is not what I want. Since the rows are identical no row should be returned. So depending on what column changes (col2 or col3), I have to alter the script. I seek advise on the method to calculate checksum. Again the PK is ID and Col1 only.


    Thanks



    drop table test
    drop table test2
    go
    --

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Script B is not correct because you have no keys in tables and, of course, it returns rows - col3s are different. There is relation many to many.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    And did you look up CHECKSUM() in BOL?

    I know you're trying to accomplish something...but you got me lost..

    It's in the same manner as your previous threads...

    Can you give us a "big picture" view of what you're trying to accomplish?

    I don't mean to offend, but you need to understan what primary keys are for...sounds like your data model is not fitting in quite right with what you're trying to accomplish...
    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.

  4. #4
    Join Date
    Sep 2003
    Posts
    176
    I think this would give you an idea of the data. Yesterday when I did the processing I had this view of the table -

    ID...County....Univ...Dept......Status

    1....A........XYZ....Accounting...Processed - Good
    1....A........ABC...Accounting...Processed - Bad
    1....A........XYZ....Marketing....Processed - Good
    1....B........PQR....HR...............Processed - Good
    1....C........XXX....HR...............Processed - Bad

    I have an index on the Status field coz I can see all Bad records on top.

    Today I have in my source system -

    ID...County....Univ...Dept

    1....A........ABC...Accounting
    1....A........XYZ....Accounting
    1....A........XYZ....Marketing
    1....B........PQR....HR
    1....C........XXX....HR
    2....C.......YYY....Training


    I want to process only those records that are new/updated since yesterday's version. I get the above records in a separate table and assign a Status to them as 'Not Processed'. I then compare the two tables. And so because of the problem stated before, I end up processing a record that I have processed the previous day.

    So how do I go about this problem? Is there a need for another column in here.

Posting Permissions

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