Range lookup functionaility
Posted: Sun Jul 05, 2009 8:27 pm
We have a requirement to retrieve data for a record based on what was active at the given time. For example
Our input record has the fields Col1 and Timestamp as such
Now we need to do a lookup against another table with Col1, StartTS and EndTS
Now for the given input, we would expect to take the second row. To implement this, we have set the Col1 on reference link to a Key and the Timestamp on primary link to a range lookup (>= StartTs, <= EndTs)
Job runs, but we get the error that duplicates are found and they will be ignored. I have confirmed there are no duplicates for the Col1, StartTS combination, as such, these duplicates must be on the Col1 and therefore it is only taking one row for each Col1 value.
This is confirmed with output not always setting the reference link columns even when there is an entry with the correct timestamp range for the given Col1.
Anyone dealt with such a requirement and got it working in DataStage? If so, were you working with DB stages or did you have it working from flatfiles/datasets? Plan to re-try on a DB stage but still awaiting the DB setup (things get done pretty quick over here!)
Our input record has the fields Col1 and Timestamp as such
Code: Select all
Col1 Timestamp
A 2009-06-01 12:00:00
Code: Select all
Col1 StartTS EndTS
A 2009-05-12 09:12:12 2009-05-18 03:12:53
A 2009-05-18 03:12:54 2009-06-02 10:10:23
A 2009-06-02 10:10:24 <null> (we set this to 2999-12-31... for the sake of the lookup)
Job runs, but we get the error that duplicates are found and they will be ignored. I have confirmed there are no duplicates for the Col1, StartTS combination, as such, these duplicates must be on the Col1 and therefore it is only taking one row for each Col1 value.
This is confirmed with output not always setting the reference link columns even when there is an entry with the correct timestamp range for the given Col1.
Anyone dealt with such a requirement and got it working in DataStage? If so, were you working with DB stages or did you have it working from flatfiles/datasets? Plan to re-try on a DB stage but still awaiting the DB setup (things get done pretty quick over here!)