Join to Hash File
Moderators: chulett, rschirm, roy
Join to Hash File
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
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
-
- Premium Member
- Posts: 457
- Joined: Tue Sep 25, 2007 4:05 pm
Re: Join to Hash File
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?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
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...
Vivek Gadwal
Experience is what you get when you didn't get what you wanted
Experience is what you get when you didn't get what you wanted
Re: Join to Hash File
Its not allowing me to put sourceFiled<= at join to hash file Key Column.
After <= I should some thing which datastage allows
After <= I should some thing which datastage allows
-
- Premium Member
- Posts: 457
- Joined: Tue Sep 25, 2007 4:05 pm
Re: Join to Hash File
I am not able to completely comprehend your question. This is how I am interpreting your job design:nivas wrote:Its not allowing me to put sourceFiled<= at join to hash file Key Column.
After <= I should some thing which datastage allows
Code: Select all
Hash File (Two key columns)
|
|
V
Source File ------------------------> XFM ----------------------> Target1
(One column in it)
Vivek Gadwal
Experience is what you get when you didn't get what you wanted
Experience is what you get when you didn't get what you wanted
Re: Join to Hash File
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.nivas wrote:I need to join to a hash file like follows.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 457
- Joined: Tue Sep 25, 2007 4:05 pm
Re: Join to Hash File
My bad.chulett wrote:You can't. A hashed join is an equi-join, period.nivas wrote:I need to join to a hash file like follows.
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.
Vivek Gadwal
Experience is what you get when you didn't get what you wanted
Experience is what you get when you didn't get what you wanted
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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
If this is NOT an option then Universe lookup/ Range lookup is the way to go but the peformance is very slow
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
If this is NOT an option then Universe lookup/ Range lookup is the way to go but the peformance is very slow