joining tables

EJRoufs · Post by **EJRoufs** » Tue Oct 05, 2004 8:51 am

I have 5 tables that are joined on a common key. On some of the tables, there is a one-to-many relationship. What is the best way to do this in DataStage? In our last version, i believe we had a "Join" stage we could use, although i never used it. We don't have it installed yet for version 7. Would that be the best way to go about joining our multiple tables together? Or is there another, better way to do this?

ray.wurlod · Post by **ray.wurlod** » Tue Oct 05, 2004 4:36 pm

If they're all in the same database, join them with SQL; you may require outer joins depending on the exact relationships.

If they're from disjoint databases, you can use the ODBC stage's multi-row return from lookup capability.

vmcburney · Post by **vmcburney** » Tue Oct 05, 2004 6:58 pm

Where there is a one to many relationship you need to decide whether you want the many rows from the linked table or just a single row, such as the most recent, from the child table.

EJRoufs · Post by **EJRoufs** » Wed Oct 06, 2004 6:14 am

ray.wurlod wrote:If they're all in the same database, join them with SQL; you may require outer joins depending on the exact relationships.

If they're from disjoint databases, you can use the ODBC stage's multi-row return from lookup capability.

Join them with SQL.... using the "Join" stage in DataStage?

chulett · Post by **chulett** » Wed Oct 06, 2004 6:25 am

Yes - Join stage, OCI stage, whatever. By the way, the Join stage is only in the PX/EE product - not the Server edition.