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

    Unanswered: Records comparsion

    Please look at the code template below for my problem. I am comparing two tables. Table 99 is the current state of the table. I get in a fresh extract of Table 99 everyday from my source - DB2 and I insert that in Table 00. Then I execute the code template below so that Table 99 stays current with the source DB2 on a daily basis.
    --
    CREATE TABLE myTable99 (
    Col1 char(1)
    , Col2 char(1)
    , Col3 char(1)
    , Col4 char(1)
    , Col5 char(1)
    , CONSTRAINT myTable99_pk PRIMARY KEY (Col1, Col2))

    CREATE TABLE myTable00 (
    Col1 char(1)
    , Col2 char(1)
    , Col3 char(1)
    , Col4 char(1)
    , Col5 char(1)
    , CONSTRAINT myTable00_pk PRIMARY KEY (Col1, Col2))
    GO

    INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
    SELECT '1','1','a','b','c' UNION ALL
    SELECT '1','2','d','e','f' UNION ALL
    SELECT '1','3','g','h','i' UNION ALL
    SELECT '1','4','j','k','l'
    --DELETED

    INSERT INTO myTable00(Col1,Col2,Col3,Col4,Col5)
    SELECT '1','1','a','b','c' UNION ALL --NO CHANGE
    SELECT '1','2','x','y','z' UNION ALL -- UPDATE
    SELECT '1','3','g','h','i' UNION ALL --NO CHANGE
    SELECT '2','3','j','b','c' --INSERT
    GO

    SELECT * FROM myTable99
    SELECT * FROM myTable00
    GO


    --DO DELETES FIRST

    DELETE FROM a
    FROM myTable99 a
    LEFT JOIN myTable00 b
    ON a.Col1 = b.Col1
    AND a.Col2 = b.Col2
    WHERE b.Col1 IS NULL AND b.Col2 IS NULL

    -- INSERT

    INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
    SELECT a.Col1, a.Col2, a.Col3, a.Col4, a.Col5
    FROM myTable00 a
    LEFT JOIN myTable99 b
    ON a.Col1 = b.Col1
    AND a.Col2 = b.Col2
    WHERE b.Col1 IS NULL AND b.Col2 IS NULL

    -- UPDATE

    UPDATE a
    SET Col3 = b.Col3
    , Col4 = b.Col4
    , Col5 = b.Col5
    FROM myTable99 a
    INNER JOIN myTable00 b
    ON a.Col1 = b.Col1
    AND a.Col2 = b.Col2
    AND ( a.Col3 <> b.Col3
    OR a.Col4 <> b.Col4
    OR a.Col5 <> b.Col5)
    GO

    SELECT * FROM myTable99
    SELECT * FROM myTable00
    GO

    DROP TABLE myTable00
    DROP TABLE myTable99
    GO


    ---
    I want to process new and updated records.
    Let me explain the problem -

    The last row for myTable99 is - 1','4','j','k','l'

    The last row for myTable00 is - '2','3','j','b','c'

    Col1 & Col2 are the PK's. So the last row from Table 99 gets deleted and the one from Table00 gets inserted as a 'New' record.Now I want to do processing only on those records that are new. So I should process record '2','3','j','b','c' .

    My processing depends on the Col3 (attribute). My problem is I do not want to process records if the attribute has been processed before. In this case Col3 has 'j' and it has been processed before in 1','4','j','k','l'. It's just that this record no longer exist. So when I get in '2','3','j','b','c' , I want to say that this record has been processed.

    How do I do that with the Insert new records query? The same case is for updates as well.

    Any help is appreciated.


    Thank you.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Records comparsion

    My processing depends on the Col3 (attribute). My problem is I do not want to process records if the attribute has been processed before. In this case Col3 has 'j' and it has been processed before in 1','4','j','k','l'. It's just that this record no longer exist. So when I get in '2','3','j','b','c' , I want to say that this record has been processed.
    You lost me.....

    Huh?

    Understand.....Whatever is in Col3 attribute BELONGS to the composite key Col1+Col2...it does not transcend the row to some other key?

    It belongs to that row...if it so happens that the same value is on another row that's fine...but it belongs to that other key....

    Can you give the explanation another shot?
    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
    Jan 2004
    Location
    Ottawa, Canada
    Posts
    53
    Why so complicated?

    Can you not flag the affected records after the import and use the EXISTS / NOT EXISTS clause to target those which have been actioned as you translate them from import source to stored data?

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I think he's saying, that if I messed with an "attribute" (bad choice of words here) of a value of 5 in column 3, then I don't want to touch ANY of the rows....across keys....right?
    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
    Sep 2003
    Posts
    176
    Here is the processing deal. I get the value of col 3 and check to see if it exists in this other table Z.

    So now when I get a new record and if I have already checked that col3 value in Table Z, then I don't really need to check it again?

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Clear as mud....what does that have to do with adding and updating rows?

    And what are you checking col3 for?
    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.

  7. #7
    Join Date
    Sep 2003
    Posts
    176
    what does that have to do with adding and updating rows?
    Well every row that is added/updated has the status of a new record. And all new records have to be processed.

    And what are you checking col3 for?
    I am checking if the col3 value of the new record exist in this other table called Table Z. If it exist then I spit out the contents of Table 99 and Table Z into another table say X. Now my client looks to see the degree of similarity in col3 and the column in Table Z. For instance - col3 value is 'j' and the column in Table Z is 'jo'

    Now for a new record which has the same value of Col3 as an old/delted record that was spit out then I have many duplicate values of Col3 (but different primary key) in X. Now the client has to check the same value of Col3 again for degree of similarity with Table Z.

    I know it's a bit crazy but that's how the data is. Let me know what you think.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I;m thinking I'm glad I'm not you....


    But a previous post by someone a good idea...

    When you add or update a record, tag it with an indicator NEW_IND for example, the do you're comparison step where the ind = 'Y' or whatever...then when you're donme set them all bacl to N

    What'chyou think?
    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.

  9. #9
    Join Date
    Sep 2003
    Posts
    176
    Well if I tag it with IND = 'Y' and do the comparison then isn't it possible that Col3 of the record with IND = 'Y' would have been compared before?

  10. #10
    Join Date
    Nov 2003
    Posts
    94
    You cannot delete the old records AND retain knowledge of the old record's values. You must retain a knowledge of what the old the old values were.

    You probably need an intermediate table indicating which of the possible values you have already processed, or implement some ki9nd of flagging system such that you identify the records to be deleted, insert the new records, compare the col3 values and then delete the flagged records.

  11. #11
    Join Date
    Jan 2004
    Location
    Ottawa, Canada
    Posts
    53
    I appologize for the tone of my previous message. I did not intend for it to sound aggressive.

    I had to address a similar problem to create change files (LDIFs) for changes to an X500 directory. Our solution was to mirror the table structure and do a comparison of changes based off of the key values for the appropriate tables.

    Prior to import (or live data manipulation) the two instances are identical. A series of EXISTS / NOT EXISTS comparisons returns the newly added or deleted records. These changses are passed to the ADD/DELETE tables.

    At the field level we passed the field names with an INNER JOIN fo the PK to look for field level changes. These changes were passed to a MODIFY table.

    I am sure that you can use a variation of this logic to solve your problem.

Posting Permissions

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