Page 1 of 1
Join to Hash File
Posted: Tue Jun 17, 2008 9:51 am
by 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
Re: Join to Hash File
Posted: Tue Jun 17, 2008 10:22 am
by vivekgadwal
nivas wrote:
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...
Re: Join to Hash File
Posted: Tue Jun 17, 2008 10:41 am
by nivas
Its not allowing me to put sourceFiled<= at join to hash file Key Column.
After <= I should some thing which datastage allows
Re: Join to Hash File
Posted: Tue Jun 17, 2008 11:50 am
by vivekgadwal
nivas wrote: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.
Re: Join to Hash File
Posted: Tue Jun 17, 2008 12:02 pm
by chulett
nivas wrote: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.
Re: Join to Hash File
Posted: Tue Jun 17, 2008 12:07 pm
by vivekgadwal
chulett wrote:nivas wrote: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.
Posted: Tue Jun 17, 2008 4:47 pm
by 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.
Posted: Tue Jun 17, 2008 5:16 pm
by 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'.
Posted: Tue Jun 17, 2008 8:14 pm
by 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
Posted: Tue Jun 17, 2008 8:15 pm
by 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