Problem with JOIN stage

Sravani · Post by **Sravani** » Thu Nov 06, 2008 12:23 am

Hi all,

We are using a JOIN stage with the inner join on a double column. Both the inputs that are coming to the JOIN stage are having hte one matched value but the output I am getting is nothing. But when I just replaced the JOIN stage with the LOOKUP stage it is working fine without any other change.

Can anybody tell me why this JOIN stage is not working whre as LOOKUP is working with the same data.

Thanks in advance,
Sravani.

kiran259 · Post by **kiran259** » Thu Nov 06, 2008 12:58 am

Comparison will be done on equality basis,actually there should not be a problem as both stages will compare the records.Some more information is needed to justify.

Sravani · Post by **Sravani** » Thu Nov 06, 2008 6:45 am

Hi Kiran,

Thanks for your response on my problem. Please see the problem details below.
1) I am getting the record count using aggregator stage from 2 types of input.
2) 1st input(sequectial file) using aggregator is giving the record count 872759
3) 2nd input(sequectial file) using aggregator is giving the record count 872759 only
4) These two values are being compared using the JOIN stage, with Inner join property and expecting the output should be is giving the count 872759 written to a sequential file in case both the values are same.
But this is not happening. It is writing nothing to the sequential file.

Note: I have verified the two record counts by writting them to different sequential files and ensured that the count is same from both the links.

For time being we have rectified this by replacing the LOOKUP stage which does the same thing. But I would like to know why the JOIN stage is not working properly.

Request to please solve my issue if anyone of you has faced this problem earlier in Information Server.

ray.wurlod · Post by **ray.wurlod** » Thu Nov 06, 2008 7:24 am

Can you please sketch out your job design? Can you use Copy or inline Peek stages and capture the output of each Aggregator for diagnostic purposes?

Sravani · Post by **Sravani** » Fri Nov 07, 2008 1:31 am

Hi Ray,

I/P File1-->TRANSFORMER--->AGGREGATOR--->COPY --->O/P1
> JOIN--->O/Pfinal
I/P File2-->TRANSFORMER--->AGGREGATOR--->COPY --->O/P2

In the above structure, I am verifying the o/p of two COPY stages which are being joined. Both the OP1 and OP2 are giving the same value(872759) as I mentioned as a NOTE, in my previous mail. But the O/Pfinal is NULL where as it should write the matched valued to the output.

But when I replace the JOIN stage with LOOKUP stage, it is writing the required o/p to the O/Pfinal.

I/P File1-->TRANSFORMER-->AGGREGATOR-->COPY --->O/P1
> LOOKUP--->O/Pfinal
I/P File2-->TRANSFORMER-->AGGREGATOR-->COPY --->O/P2

I am still not understanding why the JOIN stage is behaving strangely.

Looking for help.

pksahu · Post by **pksahu** » Mon Nov 10, 2008 8:59 am

Can you verify both datatype from the Aggregator output? Both should be same for a match.

-pksahu

[quote="Sravani"]Hi Ray,

I/P File1-->TRANSFORMER--->AGGREGATOR--->COPY --->O/P1
> [b]JOIN[/b]--->O/Pfinal
I/P File2-->TRANSFORMER--->AGGREGATOR--->COPY --->O/P2

In the above structure, I am verifying the o/p of two COPY stages which are being joined. Both the OP1 and OP2 are giving the same value(872759) as I mentioned as a NOTE, in my previous mail. But the O/Pfinal is NULL where as it should write the matched valued to the output.

But when I replace the JOIN stage with LOOKUP stage, it is writing the required o/p to the O/Pfinal.

I/P File1-->TRANSFORMER-->AGGREGATOR-->COPY --->O/P1
> [b]LOOKUP[/b]--->O/Pfinal
I/P File2-->TRANSFORMER-->AGGREGATOR-->COPY --->O/P2

I am still not understanding why the JOIN stage is behaving strangely.

Looking for help.[/quote]

pksahu · Post by **pksahu** » Mon Nov 10, 2008 8:59 am

Can you verify both datatype from the Aggregator output? Both should be same for a match.

-pksahu

[quote="Sravani"]Hi Ray,

I/P File1-->TRANSFORMER--->AGGREGATOR--->COPY --->O/P1
> [b]JOIN[/b]--->O/Pfinal
I/P File2-->TRANSFORMER--->AGGREGATOR--->COPY --->O/P2

In the above structure, I am verifying the o/p of two COPY stages which are being joined. Both the OP1 and OP2 are giving the same value(872759) as I mentioned as a NOTE, in my previous mail. But the O/Pfinal is NULL where as it should write the matched valued to the output.

But when I replace the JOIN stage with LOOKUP stage, it is writing the required o/p to the O/Pfinal.

I/P File1-->TRANSFORMER-->AGGREGATOR-->COPY --->O/P1
> [b]LOOKUP[/b]--->O/Pfinal
I/P File2-->TRANSFORMER-->AGGREGATOR-->COPY --->O/P2

I am still not understanding why the JOIN stage is behaving strangely.

Looking for help.[/quote]

Vinodanand · Post by **Vinodanand** » Mon Nov 10, 2008 10:50 am

Hi,
Can you check the dataypes that was specified, just to make sure. I tried to mimic the same scenario and worked for me without any issues.
Regards,
Vinod

Sravani · Post by **Sravani** » Mon Nov 10, 2008 11:36 pm

Hi,

Both the datatypes are same ie; Integer (11).

Thanks,

John Smith · Post by **John Smith** » Tue Nov 11, 2008 6:38 pm

Did you hash partitioned your data? Your lookup could have the entire file hashed so that would work.
just my 2 cents.