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.
lookup not unique
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 75
- Joined: Tue May 13, 2003 4:14 am
- Location: California
- Contact:
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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.
- 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.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.