Join Stage

pp · Post by pp » Fri Nov 09, 2007 10:18 am

I have 2 inputs A and B which go through a join component.
Input A
Column Name : num_rec
Data : 000123

Input B
Column Name : num_rec
Data : 000129

In my Join Component, my key is num_rec and I have specified an outer join type because in the output I want to see data from both the files. But I cannot.
I have 2 output colums num_rec_input and num_rec_ctl.

I tried putting a peek after the join and before the output file to see the data flowing and I saw the following:

Output C
num_rec_input :000123
num_rec_ctl :000000

I want to see the 000129 in the num_rec_ctl column. How can I achieve this?

ArndW · Post by **ArndW** » Fri Nov 09, 2007 2:37 pm

Have you tried declaring a column called "Data" as an output of your join?

ray.wurlod · Post by **ray.wurlod** » Fri Nov 09, 2007 4:41 pm

If num_rec is your join key you will NEVER see 000129 in a left outer join. In a left outer join you will only see matching key values (000123) or NULL.
This is the definition of a left outer join.

mcs_murali83 · Post by **mcs_murali83** » Sun Nov 11, 2007 12:00 am

Hai,

In case of join stage, meta data must be same. If we want to join two or more tables, we need a key column must be identical in all the tables. Then it works fine for you. Another use of join stage, it combines the data in sets.

mcs_murali83 · Post by **mcs_murali83** » Sun Nov 11, 2007 12:04 am

Hai,

In case of join stage, meta data must be same. If we want to join two or more tables, we need a key column must be identical in all the tables. Then it works fine for you. Another use of join stage, it combines the data in sets.

pp · Post by pp » Mon Nov 12, 2007 9:57 am

ray.wurlod wrote:If num_rec is your join key you will NEVER see 000129 in a left outer join. In a left outer join you will only see matching key values (000123) or NULL.
This is the definition of a left outer ...

I am using a full outer join.

bcarlson · Post by **bcarlson** » Mon Nov 12, 2007 11:52 am

Are you expecting to see 1 record as output? If you are using a full outer join with 2 different key values (123 and 129) I would imagine you would get 2 output records - they don't join, so you couldn't get them on the same record.

What exactly are you trying to achieve? How are 123 and 129 supposed to look on the output?

Think about how you would do this in a SQL query, because the joins in DataStage mimic that. Sometimes putting it in a different context can help figure out what you intend, and then you simply convert it into a DataStage join.

Brad.

pp · Post by pp » Mon Nov 12, 2007 1:06 pm

bcarlson wrote:Are you expecting to see 1 record as output? If you are using a full outer join with 2 different key values (123 and 129) I would imagine you would get 2 output records - they don't join, so you couldn't get them on the same record.

What exactly are you trying to achieve? How are 123 and 129 supposed to look on the output?

Think about how you would do this in a SQL query, because the joins in DataStage mimic that. Sometimes putting it in a different context can help figure out what you intend, and then you simply convert it into a DataStage join.

Brad.

Actually I am expecting two records but I am getting only 1 record.

craviraj · Post by **craviraj** » Mon Nov 12, 2007 1:16 pm

I was sure that the output would be 2 records. Still for it, I just tried a sample to check and I got the output as desired.

pp, you probably need to check that you have done a 'sort' and 'hash' on keys. As others have suggested, please check for the column names 'coz column should match exactly.

~ R-Square