UniVerse reference as input to another universe reference

scottwallace · Post by **scottwallace** » Mon Dec 19, 2005 3:32 am

Hi,

I am a DataStage newbie and am having issues with UniVerse lookups...

I am required to look up values from a hashed file based on a key, and a date range. I have the lower date range (from a primary input to a transform), but need to need to get the upper range from a hashed file, then proceed to use these as the range from a 2nd Hashed File (whcih will then return the row(s) I am after). I am using UniVerse stages for both these lookups, and have added a secondary index on the date field I am doing the range on.

Is it possible to use the output from one UniVerse stage (to receive my upper bound) as an input to another UniVerse stage (and how)? If not, any pointers on how I could get around this?

Thanks,

Scott..

kduke · Post by **kduke** » Mon Dec 19, 2005 6:37 am

Yes you can. You can easily do it with 2 transforms.

If you want to do it in one transform then the order is important. Next you have to manually type in the field or fields in the lookup columns of the second lookup because it will not let you drag a column from the first lookup. It will also leave it in red like it is a mistake but it will compile. Try it.

This has been covered before. I am not sure how to search for it though.

ray.wurlod · Post by **ray.wurlod** » Mon Dec 19, 2005 2:06 pm

You can certainly achieve the result with two Transformer stages, passing the MinValue along the link between them.

Or you could construct a user-defined nested SQL statement where the inner query returns the result used in the outer query. This is standard database practice and, used in this fashion, "UniVerse" is a standard database.

Beware that it may not be future proof. "UniVerse" will be going away as the repository database in the next release. Though I'm sure IBM would be happy to sell you a copy of UniVerse when that happens.

scottwallace · Post by **scottwallace** » Mon Dec 19, 2005 4:10 pm

Hi,

Thanks for replies, I've implemented it using 2 transforms - the first to retrieve the MaxValue which I add to the output stream and pass to the second transform which then does the "between" processing of the 2 dates.

My only problem is that this runs REALLY slow (3 rows/sec and less). I have indexed (using CREATE.INDEX, BUILD.INDEX) the date field of the first hashed file (to retrieve the MaxValue from) and also the second hashed file (that I do the "between" comparison on). Is there something that I could be missing here?

Thanks