Need Suggestions

Database
MBR
Enthusiast

Need Suggestions

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.

Regards

MBR

2 REPLIES
Enthusiast

Re: Need Suggestions

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.

SELECT...

FROM...

WHERE....

QUALIFY RANL() OVER(PARTITION BY <<column_list>> ORDER BY <<column_list>>) = 1

If you have any explicit requirement then share the details...

MBR
Enthusiast

Re: Need Suggestions

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.

Thanks

MBR