Join to Hash File

[nivas]

Hi

I need to join to a hash file like follows.

I need to use one souce filed as lookup against hash file. My hash file has two key columns.I need to join hash file as follows.

souce filed <=First key filed AND souce field>=second Key filed

Please help me out.

thanks
Nivas

[vivekgadwal]

I need to use one souce filed as lookup against hash file. My hash file has two key columns.I need to join hash file as follows.

souce filed <=First key filed AND souce field>=second Key filed

From reading your post, I am assuming that you need to put constraints, like Source field <= (less than equal to) First Key in Hash and vice versa. Am I right in assuming so?

Then, just simply drag and drop the Source field into the Derivations of both the Key columns in Hash file and put your constraints accordingly, may be in two different output links or the same input link! It would be your choice...

[nivas]

Its not allowing me to put sourceFiled<= at join to hash file Key Column.
After <= I should some thing which datastage allows

[vivekgadwal]

Its not allowing me to put sourceFiled<= at join to hash file Key Column.
After <= I should some thing which datastage allows

I am not able to completely comprehend your question. This is how I am interpreting your job design:

Code: Select all

                             Hash File (Two key columns)
                                        |
                                        |
                                        V
Source File ------------------------> XFM ----------------------> Target1
(One column in it)                        

Inside the XFM, on the inputs side, you would be having your Source File with the one column and the Hash File with two key columns. Now, tell me, where are you trying to put the "constraints" / (>=, <=) operators? In the output link or within the one of the output column derivations? If it is so, it should allow you to put operators like >, < etc. Make us understand your question in more detail.

[chulett]

I need to join to a hash file like follows.

You can't. A hashed join is an equi-join, period. Search the forums for 'range lookup' (or perhaps 'ranged lookup') to find various conversations on this topic and what your alternatives are.

[vivekgadwal]

I need to join to a hash file like follows.

You can't. A hashed join is an equi-join, period.

My bad.
I was all the time thinking that the >= and <= are going to be put as a constraint. Craig is right. You cannot have any other join with a hash file other than an equi-join.

[ray.wurlod]

You can join hashed files using a UniVerse stage. This will require that VOC pointers exist to the hashed files, and that a suitable set of join columns (one or more) exists in each hashed file.

[chulett]

And typically indexes created as well, else it will be slow as the proverbial dog. Guess I should have been more pedantic and specified a hashed file stage 'join'.

[shamshad]

If you have the ability to create a staging table in your database, create the hash file structure as a table and then use the source and the hashed Table as a SQL. Basically rather than using hash file use the data in the newly created table. This way one can PUSH the SQL to database with much faster performace.

If this is NOT an option then Universe lookup/ Range lookup is the way to go but the peformance is very slow

[shamshad]

If you have the ability to create a staging table in your database, create the hash file structure as a table and then use the source and the hashed Table as a SQL. Basically rather than using hash file use the data in the newly created table. This way one can PUSH the SQL to database with much faster performace.

If this is NOT an option then Universe lookup/ Range lookup is the way to go but the peformance is very slow