Multirow lookup between an hashed file and an oledb stage

[Alethesnake]

Hi to everyone.
Well, I know that is not possible to make a range lookup with an hashed file directly.

My situation is this.
I've a stream from an oledb stage and an hashed file as reference linked to the same transformer.
Hashed file is previously filled in with data obtained from another table. its structure is this:
id | fromValue | toValue | description
1 | -999 | -1 | undefined
2 | 0 | 60 | short term
3 | 61 | 90 | medium term
4 | 91 | 999 | long term

the maximum number of rows that this hashed file will reach is I think 10. (no intersections possible among rows as regard "from" and "to" values)

The stream have several fields, the one I'm interested in now is a value field i.e.:
value field1 field2 ...
64
99
104
..

My goal is to join the two tables in order to associate the id from the hashed file to each value field of the stream

I read in this forum that what I need is possible in two different ways:
- by using an Universe stage and performing the join in the transformer
- by preloading the data retrived from the hashed file in a before-stage routine and by using a transform function in the transformer key expression (is it right?)

I'd like to improve this second way (I read that it's better than the other one) but I haven't understood how to make the preload of the hashed file data and what my function must contain.. sincerely i'm not even sure if I've well understood the way to proceed
I looked for further information in the forum but I haven't found nothing of helpful for me.

thanks a lot for every help

[chulett]

I read in this forum that what I need is possible in two different ways:
- by using an Universe stage and performing the join in the transformer
- by preloading the data retrived from the hashed file in a before-stage routine and by using a transform function in the transformer key expression (is it right?)

Can you provide a link to the post where you found this second piece of advice? I'd be curious to see their explanation of it, and which would probably help anyone trying to help you with it.

[Alethesnake]

Sure, here are the topics I was talking about:

viewtopic.php?t=82431
(the last post)

viewtopic.php?t=84692
(4th post)

and this one, but it contains premium contents..
viewtopic.php?t=84998

The first post in particular is the one I was referring to, but I don't know where I can find "Programming with datastage BASIC"..

tks

[chulett]

Not time right now to do more than say - you should have a BASIC.pdf manual in your Docs directory. That's about all there is on the subject, so assuming that was what was meant.

[Alethesnake]

I'll take a look at the basic guide this weekend, I hope I'll find something of useful to me

[Alethesnake]

Hi,
I've looked at the Basic.pdf but it's not the same thing of "Programming with datastage BASIC" class..
Starting from the beginning I'm trying to preload my hashed file in a searchable structure..
have someone got some pieces of information regarding it? I searched the forum but I haven't found anything that could be useful to me..

thanks a lot for any help.

[WoMaWil]

Instead of greatly programming you can put the Hash instead of different rows into an array Lookup the array and then opening the array and proceed in the next step.

This is easy and quick.

[ray.wurlod]

You can use a UV stage for this. You need to "lie" that the keys are fromValue and to_Value, and to use user-defined SQL to incorporate your condition.

Code: Select all

SELECT id,fromValue,toValue,Description FROM hashedfile WHERE ? >= fromValue and ? <= toValue;

It will also be necessary that a VOC entry exists for the hashed file, so that DataStage/SQL can work at all. Search the forum for SETFILE, unless you have already created the hashed file in the account (project).