look up prob

[dr46014]

i have three source table customer,nation and region.i have created 2 hash files from customer and region.i want to populate the target table customer_d from source table.To populate nation field i am doing a look up for in nation hash file and the condition is nation.nation for customer.nationkey = nation.nationkey.again i have to populate region field region.name for customer.nationkey = nation.nationkey and nation.regionkey = region.regionkey.how can i do a look up on a look up.i cant understand this.

[ray.wurlod]

What you seek to do could be termed "cascaded lookups".

To use the GUI you need to use two Transformer stages, passing the region data from one to the next.

You can do it in one Transformer stage, but will need to type the region.regionkey reference into the second lookup's key expression; the GUI only allows you to choose input columns from the stream input link.

[dr46014]

i could not get you.how i can use two transformer stages.can you please explain in detail

[baglasumit21]

i have three source table customer,nation and region.i have created 2 hash files from customer and region.i want to populate the target table customer_d from source table.To populate nation field i am doing a look up for in nation hash file and the condition is nation.nation for customer.nationkey = nation.nationkey.again i have to populate region field region.name for customer.nationkey = nation.nationkey and nation.regionkey = region.regionkey.how can i do a look up on a look up.i cant understand this.

Take two transformer. In the first transformenr do the lookup with the nation table and pass the required columns along wiht the column nation.regionkey to the next transformer where you can perform the look up with region table and get the region coulmn

[dr46014]

i got the solution using 2 transformer stages.in this case i have only 2 dimensions thats why its easier but if i would have 10-15 dimensions and i would approach in this way there would be a performance issue.can u plz give me details how i could use a single transformer stage to achieve this.i m using server job

[DSguru2B]

If they are in the same schema then you can probably join several keys while building your hashed file.
Lets take your current situation. Build your hashed file with the following query from the database.

Code: Select all

SELECT N.nationkey, R.regionkey, <Rest of the columns that you need in your hashed file> FROM nation N
LEFT OUTER JOIN region R
ON N.nationkey = R.nationkey

This way your hashed file will have both the nationkey and region key. Basically your hashed file is a 'left outer join' of two tables.
Now in a single transformer you can do a lookup on this hashed file (keyed on nation key) and get both the columns you need. This methodology can be extended to as many tables as you need.

NOTE: Keep in mind that the temporary result of the join is stored in the database's TEMP space. Dont join too many tables at once. It will put too much load on the database. The query might not fit in the TEMP space and fail. So be smart on how many tables to join.

[dr46014]

in the user defined SQL there r two spaces for writing SQL.one is source sql and other is reference sql.what should i write where.and one more thing what should be my column definations in the hash file and which one i need to specify as key in the hash file.i have never used user defined SQL in odbc stage

[DSguru2B]

Write it in user defined sql. The key of the hased file will be whatever key is present in the source. In this case it will be nation key as thats whats present in your Customer table (source).
Get all those columns from your query that you need in the output, and the same will be the metadata of your hashed file.