Cascading Joins

[Pankaj]

Hi All,

I dont know if i can explain this.
I have three table T1, T2, T3.
Relations
T1 and T2 are related on column A (like T1.A = T2.A )
T2 and T3 are related on column Z (Like T2.z = T3.z)

Now,

When In datastage i join the T1 and T2 on A using Join Stage and then the o/p from this join stage is connected to another Join stage with T3 on Z, i do not get the correct result.

I tried the other way as well . T2 joined on T3 and then the o/p joined on T1. Still wrong.

Its like whatever T2 is joined with first, for that stage i get the o/p correct, but fails when it is joined in the second Join stage.

Any ideas as to why is this strange behavior ?

I have looked into LINK Ordering, Partioning and things like that.? NO GO !!

Any help ???

Regards
pankaj[/img]

[gh_amitava]

Hi,

There should not be any problem in this design. Check 2 things. If Z is a nullable column then handle null before join. Other is check the partitioning. Better if you use hash partition on the joining keys.

Regards
Amitava

[Pankaj]

Hi
I was thinking what happens if i do not provide any partitioning ? Do i need to do the hash partition, and i am not very sure how this works, but i am looking forward to read on this..

I tried the same thing today with some other tables... i still dont get the right thing out ... I dont know what could be wrong..



TIA

Pankaj

[Someswar Barkataki]

Hi ,

What are you not getting correctly, the required columns or required rows ?

I have used cascading joins quite a few. No problem faced till now. What kind of join are you using ? I normally use a transformer after each join and use a sequential file to validate my output. And if my output is correct, I keep the exact definition for the other output from the transformer to feed the next join. If you are feeding directly to the second join from the first join, check your output column mapping in both the join stages.

You can also breakup the job into two separate jobs and validate your output. Except specifying the key columns and join type I didn't do anything and it worked fine for me.

Good Luck,

Somesh

[gianramirez]

What should stages have for that them can make a join between two reference stages, I want that these stages make join in consecutive way.

I want to know what properties I need for can do this.
Please I need an example.


Thanks a lot.