look up problem

[dr46014]

hi all
i have a source table containing fields like id,name,location,country_id,region_id.there are also two other tables with only two columns.lets say country_table with country_id and country_name .another one region table with region_id and region_name.
i want to have a look up both for country_table and region_table.if the value of country_id and region_id matches with respective tables then country name nad region name will be populated into the target.how i can achieve this.i am not able to do it using look up.

[DSguru2B]

These are two distinct lookups. Build two hashed files. One from country_table keyed on county_id and the second from region_table keyed on region id. Perform both the lookupn on those keys in one transformer. Its a regular lookup. I dont see the confusion.

[dr46014]

can i use odbc stages as a look up or i need to convert them to hash files.

[DSguru2B]

You dont have to. It will just be faster. Or you can do everything in user defined sql. Something like

Code: Select all

select MY.id,MY.name,MY.location,MY.country_id,MY.region_id, C.country_name, R.region_name
from myTable MY left outer join country_table C
ON MY.country_id = C.country_id
left outer join region_table R
ON MY.region_id = R.region_id

But i would suggest build hashed files. It will be nice and very visible to everyone whats going on. Fancy sql tends to hide from other developers.