Problem joining four tables

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
perspicax
Premium Member
Premium Member
Posts: 26
Joined: Thu Dec 07, 2017 3:11 pm
Location: USA

Problem joining four tables

Post by perspicax »

New to DS. I am trying join 4 tables using the Join stage. The table structure are as follows

Tab_A
ID1 -->Key
xyz

TAB_B
ID1 --> Common Key for B & A
ID2 --> Common Key for B & C
ID3 --> Common Key for B & D
lmn

TAB_C
ID2 -->Key
pqr

TAB_D
ID3 -->
abc

In SQL we would join as follows

Select
*
from
TAB_A A, TAB_B B, TAB_C C, TAB_D D
where A.ID1= B.ID1
and B.ID2 = C.ID2
and B.ID3 = C.ID3

So Under properties of Join stage, I added 3 Repetitive Key as it is a 4 table join. I do not see any keys to chose from drop down to choose key for each one. So I manually type the column name to be used as Join key for each key.

But this throws following error when I run the job:

Join_XX: Error when checking operator: Key field "ID1" was not found in the view-adapted input schema

There are multiple errors like above for each key column.

Is there anything I am doing wrong?

Please let me know

Thanks
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Do the join in the source SQL just like you posted. The Join stage is really meant for disparate sources or multiple databases where the join needs to happen inside the job. Unless this is just an educational exercise?
-craig

"You can never have too many knives" -- Logan Nine Fingers
perspicax
Premium Member
Premium Member
Posts: 26
Joined: Thu Dec 07, 2017 3:11 pm
Location: USA

Post by perspicax »

Two tables are from Oracle, 1 table from SQL server and one is a file. So these are disparate sources. I am trying to achieve using Join operator/stage what the above sql would do
thompsonp
Premium Member
Premium Member
Posts: 205
Joined: Tue Mar 01, 2005 8:41 am

Post by thompsonp »

Your sql is wrong.
and B.ID3 = C.ID3 should be and B.ID3 = D.ID3

back to DataStage and are you trying to do all this is one join stage?

The inputs need to be partitioned and sorted correctly so it's not possible to mix several joins using different keys in one join stage. You'll need 3 join stages with one key column in each.

Either use multiple join stages or depending on the volume of data you might be better using lookups to avoid having to repartition and sort the data several times.
perspicax
Premium Member
Premium Member
Posts: 26
Joined: Thu Dec 07, 2017 3:11 pm
Location: USA

Post by perspicax »

Yes you are correct about the join.

Yes, I was able to make it work with 3 join stages. So we cannot use one join stage at all for this scenario?

When you say, input needs to be partitioned and sorted, do you mean, Choosing a 'collector type' (one of ordered, roundrobin, sort merge) for each input link and enabling 'perform sort' check box?
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

You can use one Join stage, but the Join key columns must be identically named (and have the same data type) to be eligible. You could use alias names in the extraction SQL to achieve this, or a Copy or Modify stage in the data streams.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
perspicax
Premium Member
Premium Member
Posts: 26
Joined: Thu Dec 07, 2017 3:11 pm
Location: USA

Post by perspicax »

My scenario doesn't work then? The join key column is different for different set of tables. For A & B it is ID1 which is identically named and have same data types, similarly for B & C it is ID2 and B & D is ID3.

The single join stage on more than two tables work only when all the tables involved are being joined on one common key?

Thanks
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Correct. And Ray noted the details for that.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply