Hi,
Case1 : We had designed a lookup logic where the data for the reference link is fetched using ODBC stage from an Oracle table. We had used ODBC stage because we wanted to make use of the property "Reference link with multi row result set".
Case2 : Then we decided to make use of Hashed File for lookup instead of making multiple database connections which happens in ODBC stage. So, we replaced the ODBC stage with UniVerse stage to read a Hashed file. The Hashed File was written in a separate job. UniVerse stage was used here to read the Hashed File because we wanted to make use of the property "Reference link with multi row result set".
After doing all these, for our surprise, the records returns from the lookup in both these cases were different. In case1, we fetch it directly from the table and in case2, the table is populated into the Hashed file and that is used for the lookup. But how can both these yield different number of records after the lookup.
How can the lookup using OBDC stage and UniVerse stage (Hashed File) give different outcome for the same source? Is there any reason for this?
Thanks.
Difference in Lookup row count - ODBC and UniVerse Stage
Moderators: chulett, rschirm, roy
No, the technique is perfectly valid. You build the hashed file with X keys and lookup on < X in the UV stage.
The reason for the difference will be the simple fact that you must not be doing the same thing in the Hashed File / UV solution that you were in the ODBC one. Without all of the gory details of your original query, the ODBC table structure, how you built the hashed file, how you defined the UV stage, what index you built, etc... we can only guess.
And I'm getting too old to guess.
The reason for the difference will be the simple fact that you must not be doing the same thing in the Hashed File / UV solution that you were in the ODBC one. Without all of the gory details of your original query, the ODBC table structure, how you built the hashed file, how you defined the UV stage, what index you built, etc... we can only guess.
And I'm getting too old to guess.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Lets hope the OP really built the hashed file with X keys and specified < X in the where predicate.chulett wrote:No, the technique is perfectly valid. You build the hashed file with X keys and lookup on < X in the UV stage.
Dont worry about guessing, Ill do it for you
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
Yes, you are correct. I would tell you the scenario. I create the Hashed File using key X. Key X is the primary key in the source table and so there is no any rows that are missed while writing in the Hashed File. Then I do a condition < Y in UV stage (where Y is a date field).
Here is all that I do in both the solution approaches.
1) In case1, I do a direct ODBC lookup, I use a query to with < Y condition. And also select the option to return multiple records on lookup match.
2) In case2, I load the data into Hashed file stage with X as key. Then read this Hashed file through a UV stage where the query is the sam as above with < Y condition. And also I have set the option to return multiple rows on lookup match too.
So, overall the logical condition is same in both the approaches.
Here is all that I do in both the solution approaches.
1) In case1, I do a direct ODBC lookup, I use a query to with < Y condition. And also select the option to return multiple records on lookup match.
2) In case2, I load the data into Hashed file stage with X as key. Then read this Hashed file through a UV stage where the query is the sam as above with < Y condition. And also I have set the option to return multiple rows on lookup match too.
So, overall the logical condition is same in both the approaches.