outer join two tables in seperate databases
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 24
- Joined: Fri Nov 25, 2005 4:16 pm
outer join two tables in seperate databases
Hi,
i have two tables, primary in oracle and reference table in SQL server. i want to perform outer(left or right or full) on these tables in server jobs.
can anyone explain how to do this. i cannot move any of the tables into other schema or database. and can i specify anything in the transformer which makes the current equii join into outer join?
i have two tables, primary in oracle and reference table in SQL server. i want to perform outer(left or right or full) on these tables in server jobs.
can anyone explain how to do this. i cannot move any of the tables into other schema or database. and can i specify anything in the transformer which makes the current equii join into outer join?
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
A normal join in a DataStage job is an outer join. The transformer simply outputs nulls onto the join fields where a lookup match is not found, you can add null handling to your output derivation fields to default values where a join is not found or leave them as null.
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
-
- Participant
- Posts: 24
- Joined: Fri Nov 25, 2005 4:16 pm
Nope the default is an outer join, you can make it an equi join by putting in contraints.battleboy1 wrote:i read that transformer performs equii join. and even in the job it is performing equii join. is anything that i can specify to make it a left or right or full outer or inner depending on what the result should be?
No shouting please.
Do you mean a left or a right outer join? As noted, with a hashed file holding the 'outer' side. Any hashed lookup miss will return nulls for all looked up columns. Turn it into an inner join by using a contraint to filter out the rows where the join returns nulls.
Do you mean a left or a right outer join? As noted, with a hashed file holding the 'outer' side. Any hashed lookup miss will return nulls for all looked up columns. Turn it into an inner join by using a contraint to filter out the rows where the join returns nulls.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 24
- Joined: Fri Nov 25, 2005 4:16 pm
-
- Participant
- Posts: 24
- Joined: Fri Nov 25, 2005 4:16 pm
the problem is the driving table is in oracle and the secondary or lookup is in sql server. so the outer is on the table in oracle or the driving table so that i do no want to miss any columns which are not matched in the look up table. if matched then get the data from lookup also. i hope you guys understood the prob.
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
You need to switch your job around making Oracle the reference link and SQL Server the primary link. Server jobs do not let your use right outer joins though parallel jobs do.
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
It is provable that A LEFT OUTER JOIN B is the same as B RIGHT OUTER JOIN A - on this basis some query tools either don't bother to provide right outer join functionality, or transparently rewrite right outer joins as left outer joins. And why not?
So heed the advice about swapping your driver and probe tables in order to achieve your desired outer join in DataStage.
So heed the advice about swapping your driver and probe tables in order to achieve your desired outer join in DataStage.
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.