outer join two tables in seperate databases

battleboy1 · Post by **battleboy1** » Wed Dec 21, 2005 5:05 pm

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?

vmcburney · Post by **vmcburney** » Wed Dec 21, 2005 5:28 pm

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.

battleboy1 · Post by **battleboy1** » Wed Dec 21, 2005 5:33 pm

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?

chinek · Post by **chinek** » Wed Dec 21, 2005 6:13 pm

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?

Nope the default is an outer join, you can make it an equi join by putting in contraints.

battleboy1 · Post by **battleboy1** » Wed Dec 21, 2005 6:48 pm

HOW TO DO LEFT OR RIGHT JOIN?

chulett · Post by **chulett** » Wed Dec 21, 2005 7:00 pm

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.

battleboy1 · Post by **battleboy1** » Wed Dec 21, 2005 7:12 pm

hey sorry guys.

i am not being rude. i just forgot to switch my capslock off.

battleboy1 · Post by **battleboy1** » Wed Dec 21, 2005 7:17 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.

vmcburney · Post by **vmcburney** » Wed Dec 21, 2005 7:26 pm

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.

ray.wurlod · Post by **ray.wurlod** » Thu Dec 22, 2005 1:06 am

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.

chulett · Post by **chulett** » Thu Dec 22, 2005 8:03 am

Which is exactly what I meant when mentioning that the 'outer' side was held in the hashed file. So, as noted, swap it around to switch from one to the other.