Page 1 of 1
Multiple values returned from the lookup
Posted: Mon Nov 09, 2009 11:30 pm
by datskosaraju
Hi guys,
I need to do a look up against a database table and if multiple records are returned for the same keys, i need to upload another table with the keys that caused this in addition to the multiple records that are returned from them.
Can anyone help me out with this?
Thanks!
Posted: Tue Nov 10, 2009 12:08 am
by swerajan
Can you please be more specific on your requirement? i understand that when there are duplicates for a key from the table you will need to join with another table with those keys and fetch the lookup value from there?
Posted: Tue Nov 10, 2009 12:38 am
by datskosaraju
If duplicates are found for a key then , i need to just write the keys to a table.
For ex
Code: Select all
table1
key1 key2
A B
A C
A D
Table2
key1 key2 value
A B 1
A B 2
A c 3
o/p Table3
errorcol1
A+B returns duplicates //(varchar field concatenation of both the keys that has duplicates)
Posted: Tue Nov 10, 2009 1:07 am
by swerajan
Option 1: Use a join stage(inner join). Partition and sort the records based on the key fields in the input link of transformer Use stage variables and check if the current records keys match with the previous records keys- if yes write the error columns with the desired value (key1 : '+' : Key2 : 'returns duplicates') else leave it blank
Option 2: Use a sort stage with key change column created for the keys. then use a transformer. In it use a stage variable to check if the keychangcolumn=1 if yes then write the erro column with the desired value (key1 : '+' : Key2 : 'returns duplicates') else leave it blank
Posted: Tue Nov 10, 2009 6:28 am
by datskosaraju
thanks for the reply,
I need to generate error not just because of the duplicate keys but if different values returned for the same set of keys!. ( in the ex keys A+ B returns values 1 & 2)
Posted: Tue Nov 10, 2009 7:31 am
by datskosaraju
sorry for confusing with the question.
The exact and brief question would be how do we check the condition whether the lookup returns multiple records?
Posted: Tue Nov 10, 2009 9:14 pm
by swerajan
ok. so if same values are returned for the same set of keys what are you supposed to do?
Posted: Tue Nov 10, 2009 10:07 pm
by Kryt0n
datskosaraju wrote:thanks for the reply,
I need to generate error not just because of the duplicate keys but if different values returned for the same set of keys!. ( in the ex keys A+ B returns values 1 & 2)
Do as per Option 2 above on the lookup stream. Doing it after the lookup sounds like a waste of processing time... and more so should your input stream be significantly larger than the likelihood of this happening.