joining key columns

[dxp]

Hi

when i am joining 3 or 4 tables with different key columns, in join stage only one key which is common for 2 tables is displaying. remaining keys which r common for other tables are not showing in key column list.
do i need to select sort on each key column with hash partitioning?

anyway i am experimenting...but meanwhile...can anybody clarify me this?

[apraman]

Are u using a single JOIN stage?
Give a clear picture of your requirement.

If you are thinking to implement the SQL queries which is written in Oracle or any other language in DS, think again?

[dxp]

Are u using a single JOIN stage?
Give a clear picture of your requirement.

If you are thinking to implement the SQL queries which is written in Oracle or any other language in DS, think again?

Hi apraman,

i am using Teradata.
i want to join 3 tables i.e. 3 Teradata Stages.
when i join first 2 teradata stages (table1, table2), the common key column will be in displayed while i am clicking 'key' filed.
after that i tried to join table3 with the same join stage.
there is common key inbetween table1 and table3 (whick is different from common key of table1 & table2).
after linking the table3 to join stage, if i click on 'key' field, the common key column for table1&table3 is not showing.

key column for table1 & table2----------->columnA
key column for table1 & table2----------->columnB

hope now u got clear picture.

[dxp]

Are u using a single JOIN stage?
Give a clear picture of your requirement.

If you are thinking to implement the SQL queries which is written in Oracle or any other language in DS, think again?

Hi apraman,

in my previous post some spell mistakes are there.....
this post is perfect....


i am using Teradata.
i want to join 3 tables i.e. 3 Teradata Stages.
when i join first 2 teradata stages (table1, table2), the common key column will be displayed while i am clicking 'key' field.
after that i tried to join table3 with the same join stage.
there is common key inbetween table1 and table3 (whick is different from common key of table1 & table2).
after linking the table3 to join stage, if i click on 'key' field, the common key column for table1&table3 is not showing.

key column for table1 & table2----------->columnA
key column for table1 & table3----------->columnB

hope now u got clear picture.

[apraman]

I didn't have opportunity to work with Terradata.

But If similar situation arised with Oracle Stage, I can defined ur problem as -
The common field exists between table 1 and 2 that means both table column called ColumnA
But there is no common field between table 1 and 3
means there is no common field having same name i.e. ColumnB should exists in both tables.

What you can do is make the name same of join key by using User defined SQL

[dxp]

I didn't have opportunity to work with Terradata.

But If similar situation arised with Oracle Stage, I can defined ur problem as -
The common field exists between table 1 and 2 that means both table column called ColumnA
But there is no common field between table 1 and 3
means there is no common field having same name i.e. ColumnB should exists in both tables.

What you can do is make the name same of join key by using User defined SQL

hi apraman,

u misinterpreted my post.

i have key 'columnA' for table1 & table2
i have key 'columnB' for table1 & table3

i.e. ...'columnB' existing in table1 & table3
'columnA' existing in table1 & table2

note:...columnA and columnB are different fields with different datatype.
.........u cant just change name to join them...

this is clear picture of my situation.

[apraman]

note:...columnA and columnB are different fields with different datatype.
.........u cant just change name to join them...

Please ignore my previous post

If you want such requirement, you can do so when all the three table should have the common fields, that means CloumnA and CloumnB should exist in table 1, 2 and 3.
From your post it can be well assumed CloumnA exists in all three. ColumnB does not exits in table 2

If want do so you need to spilt to 2 join stages.

And if u r using join type as inner please try with LOOKUP stage, where u can establish the same logic in a single lookup stage.

Thanks

[iwin]

Hi,
I think you can write a Btek script for joining tables with differnt key columns or even you can write a user defined sql to join tables.

[kumar_s]

Hi,
I think you can write a Btek script for joining tables with differnt key columns or even you can write a user defined sql to join tables.

Btek
do you mean to say Basic script or somthing different

regards
kumar

[dxp]

Hi,
I think you can write a Btek script for joining tables with differnt key columns or even you can write a user defined sql to join tables.

Btek
do you mean to say Basic script or somthing different

regards
kumar

B.TE.Q--------BTEQ-------->Basic Teradata Query Tool.

[dxp]

note:...columnA and columnB are different fields with different datatype.
.........u cant just change name to join them...

If want do so you need to spilt to 2 join stages.

And if u r using join type as inner please try with LOOKUP stage, where u can establish the same logic in a single lookup stage.

Thanks

hi apraman,

thanks for giving alternatives...actually i tried those.
but i just wanted to know how to do it in PX jobs using Join Stage.

[apraman]

You can try by creating a dummy field ColumnB with NULL value for table 2 using User Defined SQL. This will make both columnsA and B to be common in all three input links of the join stage, then Try to use Left/right outer join. I never tried it, You can experiment. But some consideration u need to take like selection of only those record of table 3 for ColumnB which have values in table 1.
Pls let me know whether it works or not.

[track_star]

The reason that you don't see the columns is that the GUI is designed to display only the columns that are common to ALL input links. Since you only have one col common to table A and table B, as well as a different one common to table A and table C, you don't see any values in the drop-down list when all three tables are inputs to the join. Try separating the joins to take A & B in the first join, and join on the common key, then take that output to the next join where you hook up with C and use the column common to A & C. While the join stage can accept more than two inputs, it works best when there are only two......

[ray.wurlod]

While it's true that the Join stage can have more than two input links, they do require a common key, as track_star has pointed out.

One solution would be to use two cascaded Join stages; one to join tables A and B, the second to join that result and table C. Or you could use three Join stages: A and B, A and C, result with result (though a Funnel stage might suffice for the last of these, depending on your requirements.

As others have pointed out, you could also construct the join in SQL and have the database perform it. This approach assumes that all three tables are in the same database instance, of course. This can be done as generated SQL; you don't need user-defined SQL just to do joins.

[dxp]

While it's true that the Join stage can have more than two input links, they do require a common key, as track_star has pointed out.
.

Hi ray.wurlod,

is there any specific reason why this facility is not provided in join stage.
because right now in my work, i frequently encounter this situation of joining 3 or more tables. i have to use no. of joins in a job. and this will affect the performance, because no. of database connections will be more as join stages increase.

any thoughts!!?