lookup not unique

[acool]

Hi everyone,

I have a question about lookup. If the lookup item is not unique, will it return an error, or it is going to return the first value found.

[gh_amitava]

Hi,

Lookup will return all values..

Regards
Amitava

[vmcburney]

I suggest you move your lookup data into a hash file where the key of the hash file matches the lookup fields. This ensures you only get one row back for each lookup. In your hash file load job put in the logic that selects the correct row where there are duplicates, for example:
- Push data through an aggregation stage to take min/max/sum values where duplicate rows exist.
- Use a sort stage to order the data to take the first or last row where duplicates exist.
- Use a database sub query to identify which row to take where duplicates exist.
- etc

It is better to plan up front what row you want to see returned by a lookup where multiple rows may exist and to build a hash file that only has those rows in it.

[ray.wurlod]

If you WANT multi-row lookup capability, use either ODBC or UV stage.

How any other stage type works depends on what the database server delivers, but typically it's the first match found that DataStage uses.

A hashed file, necessarily, has UNIQUE primary key values. If you want a secondary key lookup to return multiple rows from a hashed file, use a UV stage to do so.

[elavenil]

Hi,

If you are using PX Lookup stage, there is an option to get all values assigned to the source records if you have duplicates in the lookup and the option is 'Allow Duplicates" set to =True then you will get all lookup values.

Regards
Saravanan