first of all thanks to seeing my post
i am new to Teradata
my Issue is :
we have lookups for to maintaining the unique keys
in staging we have duplicates values which is loaded into lookups too as a unique value
lets say in staging empid is having same number 15 times and lookup we have loaded only one value that is unique
in joining condition we are using same column this is getting 15 values i am getting by joining
i need only one value which is matched
how can i do this
can any one give suggestions how to achieve this.
Thanks in advance.
This is logical. If there is one value in lookup table and there are 15 values in joining tables then you get the 15 records.
If you need only single record from the main table then you can use the ROW_NUMBER or RANK and get the single record. Both of these functions assign sequential unique number to the records.
QUALIFY RANL() OVER(PARTITION BY <<column_list>> ORDER BY <<column_list>>) = 1
If you have any explicit requirement then share the details...
Thanks Aftab for replying
Actually we are implementing scd type2 mechanism , we are loading the data into targets from different tables that too from each table once at a time
lets say suppose party is there , this is loading from 3 tables
from these 3 tables we load unique values into lookup
then from each table we start loading into targets
first we will load new records which is matched with lookups
then we will take the updates from different table then we will join with lookup and target , here the records which is matched with lookup and target those records only will come , these records will stroe in gtt table.
then we will update by update statement
here i m getting issues.. if gtt having 1000 records(which is taken from SU(updates) and also matching with targets and lookups)
when i am firing update statement 500 records got updated.this is i am getting issue.
can you give me some suggestions for implementing scd type2, we are doing through bteq.