Lookup fails when there is duplicate records in source

• s.appanasamy

  SSC Enthusiast

  Points: 147

  More actions

  February 23, 2009 at 2:06 am

  #128535

  Hi ,

  I am doing incremental load from source table "A" to destination table "B".

  My source table has duplicate records for a key column. So, while doing lookup , lookup identifies all duplicate rows as new rows and inserts all rows to destination table.

  How can I avoid that?

  Please help

• Rick Todd

  Hall of Fame

  Points: 3302

  More actions

  February 23, 2009 at 9:20 am

  #948687

  Maybe there's just not enough information here for this to not be an obvious idea, but SELECT DISTINCT from your source table?

  
  Rick Todd

• s.appanasamy

  SSC Enthusiast

  Points: 147

  More actions

  February 24, 2009 at 7:05 am

  #949317

  Hi Rick,

  My Source table structure is like this:

  ID (Primary Key) , MGR_Id , MGR_NAME , MGR_DOJ

  Values are :

  11XXX 2001-01-11

  22bbb 2009-02-23

  33ccc 2009-02-23

  43ddd 2009-02-23

  I have to incrementaly load (Type 2) my Target :

  Which contains cols like

  ID , MGR_Id , MGR_NAME , MGR_DOJ

  But while using SCD transformation for loading target, it inserts all the records in Target. ie, after the load Target table look like

  11XXX 2001-01-11

  22bbb 2009-02-23

  33ccc 2009-02-23

  43ddd 2009-02-23

  Could you please tell me why there is two records for MGR_ID = 3.

  Please provide your inputs

• Rick Todd

  Hall of Fame

  Points: 3302

  More actions

  February 24, 2009 at 7:09 am

  #949322

  s.appanasamy (2/24/2009)

  ---

  But while using SCD transformation for loading target, it inserts all the records in Target. ie, after the load Target table look like

  11XXX 2001-01-11

  22bbb 2009-02-23

  33ccc 2009-02-23

  43ddd 2009-02-23

  So, you would like it to insert

  11XXX 2001-01-11

  22bbb 2009-02-23

  43ddd 2009-02-23

  or

  11XXX 2001-01-11

  22bbb 2009-02-23

  33ccc 2009-02-23

  or what?

  
  Rick Todd

• s.appanasamy

  SSC Enthusiast

  Points: 147

  More actions

  February 26, 2009 at 6:19 am

  #950541

  Hi Rick,

  Thanks for your reply...

  I would like it to insert like (as in first scenario you mentioned)

  1 1 XXX 2001-01-11

  2 2 bbb 2009-02-23

  4 3 ddd 2009-02-23

  Please tell me how can I achieve this using look up.

  Thanks in advance!!

• Rick Todd

  Hall of Fame

  Points: 3302

  More actions

  February 26, 2009 at 1:17 pm

  #950809

  s.appanasamy (2/26/2009)

  ---

  Hi Rick,

  Thanks for your reply...

  I would like it to insert like (as in first scenario you mentioned)

  1 1 XXX 2001-01-11

  2 2 bbb 2009-02-23

  4 3 ddd 2009-02-23

  Please tell me how can I achieve this using look up.

  Thanks in advance!!

  It sounds like maybe you're trying to do an initial load where you just want the most recent version of each Manager, then you want to just keep the most recent version?

  If that's the case, change your source query to only take the most recent row:

  SELECT DISTINCT MGR_Id

  , MGR_NAME

  , MGR_DOJ

  FROM tablename tn

  WHERE ID = (SELECT MAX(x.ID)

  FROM tablename x

  WHERE x.MGR_ID = tn.MGR_ID )

  Then create the SCD lookup using Type 1 (Changing Attribute) for all the attributes, which is probably what you already have, and use that going forward. It should then maintain only the latest value for each MGR_ID.

  Hope that helps,

  
  Rick Todd