Query regarding Look Up Transformation-Look up transformation fetching multiple matched records from ther reference table

Question

Query regarding Look Up Transformation-Look up transformation fetching multiple matched records from ther reference table

Smash125

Hall of Fame

Points: 3562
More actions
August 11, 2012 at 12:14 pm

#392936

Look up transformation fetching multiple matched records.More information as below
1. Input data comes from a table called Contact(ContactID is unique in this table) and this data is cached using cache transform
CREATE TABLE Contact
(
FirstName NVARCHAR(255),
MiddleName NVARCHAR(255),
LastName NVARCHAR(255),
ContactId INT
)
2. Reference data comes from table called Sales(ContactId is comes more than one times or we can say between Contact table and Sales table there is one to many relationship) table structure as below
CREATE TABLE Sales
(
ContactID INT,
TotalDue MONEY
)
3.In Sales table there are duplicate rows for Contactid 1
1 5370.284
1 5370.284
1 5370.284
1 5370.284
1 5370.284
1 5370.284
1 5370.284
when i create package using look transformation
in Look Up Matched Output pulls all the rows
(duplicate) for ContactID 1from Sales table.As per definition 'If there are multiple matches in the reference table, the Lookup transformation returns only the first match returned by the look up query.'
What could be the issue

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Shouldn't it be the other way around? Sales is the input data and Contact is the reference table?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Smash125 Hall of Fame Points: 3562 More actions · Answer 2

No then you will have unique rows for each look up id or Contactid

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Smash125 (8/13/2012)
No then you will have unique rows for each look up id or Contactid

Isn't the whole point of a reference table that it's IDs are unique, so that you can lookup reference values belonging to a particular ID in the input stream?

I don't understand why you'd use the sales table as the lookup table, as it's the Contact table that holds all the information about contacts.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Smash125 Hall of Fame Points: 3562 More actions · Answer 4

You are right koen.Initially i got it entirely wrong about my understanding about look up transformation. What i was trying to do

Sales Table as input table and Contact table as reference table. obviously it is going to pull duplicate rows since there is Many - One relationship.It has to be other way as you suggested earlier.