Achieving Right Outer join in a Server Job
Moderators: chulett, rschirm, roy
Achieving Right Outer join in a Server Job
Hello friends,
Can I perform Right Outer join in a Server job.
Thank you
-Yamini
Can I perform Right Outer join in a Server job.
Thank you
-Yamini
Can you be more specific regarding your request? Do you intend to accomplish this with a hash lookup or user written SQL in a stage?
Mike Hester
mhester@petra-ps.com
mhester@petra-ps.com
Achieving Right Outer join in a Server Job
Hi Micheal,
I intend to perform the right outer join using a Hash file stage.
Can you help me with this
Thanks in advance
-Yamini
I intend to perform the right outer join using a Hash file stage.
Can you help me with this
Thanks in advance
-Yamini
Identify the columns or metadata on the target link with all of the columns you want. Perform a lookup against the hash file and interrogate if the lookup was successful by checking LKUP_LINK.NOTFOUND. If the lookup was successful then the target link would be populated with the values from the lookup file. If the lookup was not successful then you would assign @NULL to these columns.
Do not constrain on whether the lookup was successful or not because you want every row to flow through, but with null values for some columns when the lookup failed.
Does this seem reasonable?
Regards,
Do not constrain on whether the lookup was successful or not because you want every row to flow through, but with null values for some columns when the lookup failed.
Does this seem reasonable?
Regards,
Mike Hester
mhester@petra-ps.com
mhester@petra-ps.com
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
All hashed file lookups are LEFT outer joins (the row returned from the reference input link contains NULL in every column if the key is not matched).
It is provable that any RIGHT outer join can be rewritten as a LEFT outer join, but that word "rewritten" says it all; your job design would require radical change.
It is provable that any RIGHT outer join can be rewritten as a LEFT outer join, but that word "rewritten" says it all; your job design would require radical change.
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.
Achieving Right Outer join in a Server Job
Hi Micheal,
I tried the logic you specified but still getting the result of Left outer join. Can you explain me clearly how to get all rows from the Hash file.
Ray, Can you explain me the changes I have to make to the job to achieve right outer join
Thank you
-Yamini
I tried the logic you specified but still getting the result of Left outer join. Can you explain me clearly how to get all rows from the Hash file.
Ray, Can you explain me the changes I have to make to the job to achieve right outer join
Thank you
-Yamini
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
If you want to do a right outer join then you have to reverse your input and lookup links to turn it into a left outer join. This turns your hash file into an input stream instead of a reference lookup. It means turning your input stream into a lookup. If it is a database table you can do a direct reference lookup, otherwise you may have to load it into a hash file.
Enterprise Edition parallel jobs give you a a few choices for right outer joins but server edition gives you no choices.
Enterprise Edition parallel jobs give you a a few choices for right outer joins but server edition gives you no choices.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
I was not clear in my post, but you do need to redesign your job as Ray and others posted. Your source now needs to be the lookup and the lookup should now be the source. If your job is not too terribly complicated then I would think that this should be quite easy to accomplish.
Mike Hester
mhester@petra-ps.com
mhester@petra-ps.com
Achieving Right Outer join in a Server Job
Hi Micheal,
The main problem for me to implement your logic is the key columns in the Hash file. If I change the Hash file to Sequential file and vice versa, I run into the scenario where the Hash file does not have all the columns of the Sequential file
For example:
This is the job design if I make the above change.
HASHFILE
|
|
|
Transformer
|
|
|
SOURCE_FILE------------->TARGET_FILE
For example, the key columns of the Hash file are:
Name, Dept, SID
The columns in the SOURCE_FILE are
Name, SID
The columns in the TARGET_FILE are
Name, Dept, SID
Is there a way to perform look up with the matching columns?
Thank you
-Yamini
The main problem for me to implement your logic is the key columns in the Hash file. If I change the Hash file to Sequential file and vice versa, I run into the scenario where the Hash file does not have all the columns of the Sequential file
For example:
This is the job design if I make the above change.
HASHFILE
|
|
|
Transformer
|
|
|
SOURCE_FILE------------->TARGET_FILE
For example, the key columns of the Hash file are:
Name, Dept, SID
The columns in the SOURCE_FILE are
Name, SID
The columns in the TARGET_FILE are
Name, Dept, SID
Is there a way to perform look up with the matching columns?
Thank you
-Yamini
Is the SID unique? If not and you require all three (3) columns for your key then you have an issue and one that would be present regardless of tool used to join the tables. If SID is unique then you could build the Hash with the SID as the key and Name and Dept as fields or columns.
You need to have some common key between the source stream and the lookup for this to work using a Hash lookup no matter which file is being used as input.
Regards,
You need to have some common key between the source stream and the lookup for this to work using a Hash lookup no matter which file is being used as input.
Regards,
Mike Hester
mhester@petra-ps.com
mhester@petra-ps.com