Page 1 of 1
If Multiple records retuns from lookup source record
Posted: Mon Apr 27, 2009 12:10 am
by prams
HI
I have a requirement like
i am getting records from source and next stage i doing lookup with different tables with different columns.
If lookup retuns multiple records i need to reject the source record.
so how to achieve this desin can any one tell me please.
Thanks in Advance
Prams
Posted: Mon Apr 27, 2009 12:48 am
by Kryt0n
Not sure if any DS stage can do such but quickest way that springs to mind is a pre-check identifying how many records meet the join criteria. Preferably only hold those that should be rejected and reject when found (assuming the number to be rejected is minimal compared to the total number of rows)
Re: If Multiple records retuns from lookup source record
Posted: Mon Apr 27, 2009 2:13 am
by Pagadrai
prams wrote:
If lookup retuns multiple records i need to reject the source record.
Hi,
If your lookup is a table, you can write the lookup query in this way:-
1) Group by the columns that you are going to use as lookup keys
2) obtain the count also along with the other required values
3) add a 'Having' clause where count = 1.
In you job, you can reject the records that dosent match the lookup.
NOTE: This scenario works unless you want to capture the two rejects seperately:-
Lookup records dosent exist and
Lookup has more than one record matching.