DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
perspicax
Participant



Joined: 07 Dec 2017
Posts: 5
Location: USA
Points: 56

Post Posted: Thu Dec 07, 2017 4:00 pm Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
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

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42475
Location: Denver, CO
Points: 218489

Post Posted: Thu Dec 07, 2017 5:20 pm Reply with quote    Back to top    

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

I know I don't say this enough, but I like when you talk to me. It's much better than when nobody talks to me. Or when people that I don't like will not stop talking to me.
Rate this response:  
perspicax
Participant



Joined: 07 Dec 2017
Posts: 5
Location: USA
Points: 56

Post Posted: Thu Dec 07, 2017 5:37 pm Reply with quote    Back to top    

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
Rate this response:  
thompsonp



Group memberships:
Premium Members

Joined: 01 Mar 2005
Posts: 192

Points: 1760

Post Posted: Fri Dec 08, 2017 5:50 am Reply with quote    Back to top    

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.
Rate this response:  
perspicax
Participant



Joined: 07 Dec 2017
Posts: 5
Location: USA
Points: 56

Post Posted: Fri Dec 08, 2017 4:33 pm Reply with quote    Back to top    

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?
Rate this response:  
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54166
Location: Sydney, Australia
Points: 293772

Post Posted: Fri Dec 08, 2017 8:03 pm Reply with quote    Back to top    

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 ...

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne
Rate this response:  
perspicax
Participant



Joined: 07 Dec 2017
Posts: 5
Location: USA
Points: 56

Post Posted: Mon Dec 11, 2017 3:37 pm Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 42475
Location: Denver, CO
Points: 218489

Post Posted: Mon Dec 11, 2017 7:59 pm Reply with quote    Back to top    

Correct. And Ray noted the details for that.

_________________
-craig

I know I don't say this enough, but I like when you talk to me. It's much better than when nobody talks to me. Or when people that I don't like will not stop talking to me.
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours