All Forums Database
quest 20 posts Joined 12/09
07 Jul 2016
Any dynamic lookup concept in teradata?

Hi all,
Was wondering if there is anything like dynamic lookup concept where duplicates from source can be prevented from loading into target table?
Source table
col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | col | col9 | col10
Intermediate table (For SCD-2 identifying columns are col1, col2, col4)
col1 | col2 | col3 | col4 | col5 | col6 | col7 | col8 | col | col9 | col10 | start_dt | end_dt
Target table (For SCD-2 identifying columns are col3, col5 and col9)
col3 | col5 | col9 | start_dt | end_dt
So basically while populating intermediate table, if changes are found in col1, col2 and col4 then only new record will be inserted and old record will be retired. But here there is every possibility that for both new and old records, col3, col5 and col9 will remain the same. So while we populate target table, we might populate multiple duplicates inspite of these records being different in both source and intermediate table. So is there any mechanism to avoid duplicates from being populated in the intial load of the target table?
In case of informatica, i can use dynamic lookup and avoid duplicates from getting populated.
Thanks!
 

AtardecerR0j0 71 posts Joined 09/12
13 Jul 2016
--For SCD-2 identifying columns are col1, col2, col4

select normalize col1, col2, col4, period( start_dt, end_dt )
from table1;

 

Be More!!

You must sign in to leave a comment.