Multiple values returned from the lookup

datskosaraju · Post by **datskosaraju** » Mon Nov 09, 2009 11:30 pm

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!

swerajan · Post by **swerajan** » Tue Nov 10, 2009 12:08 am

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?

datskosaraju · Post by **datskosaraju** » Tue Nov 10, 2009 12:38 am

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)

swerajan · Post by **swerajan** » Tue Nov 10, 2009 1:07 am

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

datskosaraju · Post by **datskosaraju** » Tue Nov 10, 2009 6:28 am

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)

datskosaraju · Post by **datskosaraju** » Tue Nov 10, 2009 7:31 am

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?

swerajan · Post by **swerajan** » Tue Nov 10, 2009 9:14 pm

ok. so if same values are returned for the same set of keys what are you supposed to do?

Kryt0n · Post by **Kryt0n** » Tue Nov 10, 2009 10:07 pm

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.