Archives of the TeradataForum

Message Posted: Thu, 20 Dec 2007 @ 23:01:10 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Bill,

I've never seen anything like this in Teradata to looks for changes on a table.

I do a lot these type of comparisions to find out Inserted, Deleted and Updated rows between two days data. Generally we don't find them to be too expensive and they run quite quikly, comparing two table with several million rows. I done comparisons of the method below bewteen PL1, mainframe file compare (Compare X) & Teradatam, Teradata won hands down.

The general process that I use to compare two tables, one from today (called Customer_New) and one from yesterdy (Called Customer_Old) is as follows

1) Create Delta table

2) Use a MSR (multi statment request) to find inserts, deletes and updates

- If the Primary Key is on the new table and not on the old table it's a Instert

- If the Primary Key is on the old table and not on the new table it's a Delete

- If the primart key is on both table but not every thing is the same it's an update.

- Note "Primary Key" not primary index, in this case the primary key is the UPI Cust_Key

- You must also be careful of nulllible fields on the update check (because null is not equal to null) and cloalesce the null value to some thing.

3) Because the you are using an MSR into an empty table it will be very quick and you can apply your changes to where ever you want.

worked example

     DDL of new and old tables
     CREATE SET TABLE Customer_New
     ,NO FALLBACK
     ,NO BEFORE JOURNAL
     ,NO AFTER JOURNAL
        (
       CUST_KEY               INTEGER
     ,CUST_NAME            CHAR(30)
     ,CUST_ADDR_LINE1        CHAR(30)
     ,CUST_ADDR_LINE2        CHAR(30)
     ,CUST_ADDR_LINE3        CHAR(30)
     ,CUST_ADDR_LINE4        CHAR(30)
     ,CUST_ADDR_LINE5        CHAR(30)
     ) UNIQUE PRIMARY INDEX  (CUST_KEY)
     CREATE SET TABLE Customer_OLD
     ,NO FALLBACK
     ,NO BEFORE JOURNAL
     ,NO AFTER JOURNAL
        (
       CUST_KEY               INTEGER
     ,CUST_NAME            CHAR(30)
     ,CUST_ADDR_LINE1        CHAR(30)
     ,CUST_ADDR_LINE2        CHAR(30)
     ,CUST_ADDR_LINE3        CHAR(30)
     ,CUST_ADDR_LINE4        CHAR(30)
     ,CUST_ADDR_LINE5        CHAR(30)
     ) UNIQUE PRIMARY INDEX  (CUST_KEY)
     DDL of delta table
     CREATE SET TABLE DELTA_TABLE
     ,NO FALLBACK
     ,NO BEFORE JOURNAL
     ,NO AFTER JOURNAL
        (
       CUST_KEY               INTEGER
     ,CUST_NAME            CHAR(30)
     ,CUST_ADDR_LINE1        CHAR(30)
     ,CUST_ADDR_LINE2        CHAR(30)
     ,CUST_ADDR_LINE3        CHAR(30)
     ,CUST_ADDR_LINE4        CHAR(30)
     ,CUST_ADDR_LINE5        CHAR(30)
     ,UPDATE_TYPE               Char(01)
     ) UNIQUE PRIMARY INDEX  (CUST_KEY)

Look for deltas

     INSERT INTO DELTA_Table
     (   CUST_KEY
     ,CUST_NAME
     ,CUST_ADDR_LINE1
     ,CUST_ADDR_LINE2
     ,CUST_ADDR_LINE3
     ,CUST_ADDR_LINE4
     ,CUST_ADDR_LINE5
     ,UPDATE_TYPE)
     Select
       new.CUST_KEY
     ,new.CUST_NAME
     ,new.CUST_ADDR_LINE1
     ,new.CUST_ADDR_LINE2
     ,new.CUST_ADDR_LINE3
     ,new.CUST_ADDR_LINE4
     ,new.CUST_ADDR_LINE5
     ,'I'
       From Customer_New   new
     where new.cust_key not in (select cust_key from customer_old)
     ;INSERT INTO DELTA_Table
     (   CUST_KEY
     ,CUST_NAME
     ,CUST_ADDR_LINE1
     ,CUST_ADDR_LINE2
     ,CUST_ADDR_LINE3
     ,CUST_ADDR_LINE4
     ,CUST_ADDR_LINE5
     ,UPDATE_TYPE)
     Select
       old.CUST_KEY
     ,old.CUST_NAME
     ,old.CUST_ADDR_LINE1
     ,old.CUST_ADDR_LINE2
     ,old.CUST_ADDR_LINE3
     ,old.CUST_ADDR_LINE4
     ,old.CUST_ADDR_LINE5
     ,'D'
       From Customer_Old   Old
     where old.cust_key not in (select cust_key from customer_new)
     ;INSERT INTO DELTA_Table
     (   CUST_KEY
     ,CUST_NAME
     ,CUST_ADDR_LINE1
     ,CUST_ADDR_LINE2
     ,CUST_ADDR_LINE3
     ,CUST_ADDR_LINE4
     ,CUST_ADDR_LINE5
     ,UPDATE_TYPE)
     Select
       new.CUST_KEY
     ,new.CUST_NAME
     ,new.CUST_ADDR_LINE1
     ,new.CUST_ADDR_LINE2
     ,new.CUST_ADDR_LINE3
     ,new.CUST_ADDR_LINE4
     ,new.CUST_ADDR_LINE5
     ,'U'
       From Customer_New   New
     inner join customer_old Old
     on new.cust_key = old.cust_key
     where
     (  new.CUST_NAME  <> old.CUST_NAME
     or new.CUST_ADDR_LINE1 <> old.CUST_ADDR_LINE1
     or new.CUST_ADDR_LINE2 <> old.CUST_ADDR_LINE2
     or new.CUST_ADDR_LINE3 <> old.CUST_ADDR_LINE3
     or new.CUST_ADDR_LINE4 <> old.CUST_ADDR_LINE4
     or new.CUST_ADDR_LINE5 <> old.CUST_ADDR_LINE5
     );

Fachtna O'Donovan
AIB Bank
Ireland

	<Prev	Next>		<<First	<Prev	Next>	Last>>