August 11, 2012 at 12:14 pm
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
August 13, 2012 at 12:50 am
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
August 13, 2012 at 9:20 am
No then you will have unique rows for each look up id or Contactid
August 13, 2012 at 2:04 pm
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
August 14, 2012 at 12:11 am
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply