limit the lookup data in a hashed file

eldonp · Post by **eldonp** » Sat Oct 06, 2007 1:54 am

there should be some way to limit the lookup data that is written to a hashed file.

e.g.

i extract 100 000 rows from a source (lets assume a subset of the customer base) and need to do a lookup on the latest order from a table in a different system with 10 000 000 rows (lets assume its the orders summary table). It would be beneficial to pass the list of account numbers into the job that creates the hashed file. This means that I only load 100 000 rows into the lookup, not records for each customer!

ray.wurlod · Post by **ray.wurlod** » Sat Oct 06, 2007 3:56 am

Limit in the SQL that extracts data to load in the hashed file.

Code: Select all

SELECT ID, othercols FROM sourcetable T1 WHERE EFF_DATE = (SELECT MAX(EFF_DATE) FROM sourcetable T2 WHERE T1.ID = T2.ID)

eldonp · Post by **eldonp** » Mon Oct 08, 2007 5:36 am

uhhh, the lookup data is sourced from another system - i.e another database. Then the inline sub-query won't work.

chulett · Post by **chulett** » Mon Oct 08, 2007 6:49 am

Make it work. Load it into a work table in your database so you can do this - a pretty common technique, actually.

ray.wurlod · Post by **ray.wurlod** » Mon Oct 08, 2007 2:31 pm

... or even a UV table in DataStage