Join stage
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 7
- Joined: Mon Oct 23, 2006 4:44 am
Join stage
What I did:
I did a Right outer join on two input files using join stage in to a dataset.
I partitioned both inputs by "Hash" on key using inlink partition.
Observation:
When I sort on keys in Ascending order, the stage works fine.
When I sort on keys in Descending order, the stage doesn't give the expected result and also throws the following warning.
Warning:
Join: When checking operator: User inserted sort "Join.Left_Link_Sort" does not fulfill the sort requirements of the downstream operator "APT_JoinSubOperator in Join"
Question:
Is descending order not applicable to "Join" stage.
I tried and couldn't get a solution from Online manuals and a search on this Forum.
Thanks in advance,
Lakshman.
I did a Right outer join on two input files using join stage in to a dataset.
I partitioned both inputs by "Hash" on key using inlink partition.
Observation:
When I sort on keys in Ascending order, the stage works fine.
When I sort on keys in Descending order, the stage doesn't give the expected result and also throws the following warning.
Warning:
Join: When checking operator: User inserted sort "Join.Left_Link_Sort" does not fulfill the sort requirements of the downstream operator "APT_JoinSubOperator in Join"
Question:
Is descending order not applicable to "Join" stage.
I tried and couldn't get a solution from Online manuals and a search on this Forum.
Thanks in advance,
Lakshman.
-
- Participant
- Posts: 83
- Joined: Sat Oct 28, 2006 6:25 am
-
- Participant
- Posts: 7
- Joined: Mon Oct 23, 2006 4:44 am
Re: Join stage
Yep.tagnihotri wrote:Have you sorted both the links in same order ?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
There's no reason descending order should not work, provided that you really have specified partitioning and sorting exactly the same for both links, or Entire on the right input (which carries almost no penalty in an SMP environment).
What do you mean by "does not work"? Are the appropriate columns nullable, since they may receive NULL as the result of the outer join?
What do you mean by "does not work"? Are the appropriate columns nullable, since they may receive NULL as the result of the outer join?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 7
- Joined: Mon Oct 23, 2006 4:44 am
Thanks a lot. I checked again. I have specified partitioning and sorting exactly the same way. Also now i tried with "Entire" partition for the "Right" input, Still it doesn't.ray.wurlod wrote:There's no reason descending order should not work, provided that you really have specified partitioning and sorting exactly the same for both links, or Entire on the right input (which carries almost ...
-
- Participant
- Posts: 7
- Joined: Mon Oct 23, 2006 4:44 am
Re: Join stage
sorted in descending order + then joined ryt
check the join component sort options .it should work...
please let me know ..
check the join component sort options .it should work...
please let me know ..
lakshmEn@gmail.com wrote:What I did:
I did a Right outer join on two input files using join stage in to a dataset.
I partitioned both inputs by "Hash" on key using inlink partition.
Observation:
When I sort on keys in Ascending order, the stage works fine.
When I sort on keys in Descending order, the stage doesn't give the expected result and also throws the following warning.
Warning:
Join: When checking operator: User inserted sort "Join.Left_Link_Sort" does not fulfill the sort requirements of the downstream operator "APT_JoinSubOperator in Join"
Question:
Is descending order not applicable to "Join" stage.
I tried and couldn't get a solution from Online manuals and a search on this Forum.
Thanks in advance,
Lakshman.
chandra ,
Hyd
Hyd
-
- Participant
- Posts: 7
- Joined: Mon Oct 23, 2006 4:44 am
Re: Join stage
I introduced "Sort stage" before my "Join" stage.
Now my "Join" stage has no inlink sort. It just does "Auto" partition.
In the "Sort" stage, if I sort in ascending order, it works.
In the "Sort" stage, if I sort in descending order, it doesn't.
Now my "Join" stage has no inlink sort. It just does "Auto" partition.
In the "Sort" stage, if I sort in ascending order, it works.
In the "Sort" stage, if I sort in descending order, it doesn't.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 7
- Joined: Mon Oct 23, 2006 4:44 am
Now i introduced "Peek" stage like below to the job which actually was working wrong with sort order as descending.
Left_Input_file -> Sort stage -> Peek ->
Join -> Output
Right_Input_file -> Sort stage -> Peek ->
To my surprise, it ran fine and gave the expected result.
Now i removed the peek and still surprised to see that it works fine.
My Objective got resolved but doubt still remains. What could have happened by the mere introduction on "Peek"??? If any body can get a clue kindly update me.
Left_Input_file -> Sort stage -> Peek ->
Join -> Output
Right_Input_file -> Sort stage -> Peek ->
To my surprise, it ran fine and gave the expected result.
Now i removed the peek and still surprised to see that it works fine.
My Objective got resolved but doubt still remains. What could have happened by the mere introduction on "Peek"??? If any body can get a clue kindly update me.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Code: Select all
select * from users where clue > 0;
0 rows returned.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
ray.wurlod wrote:Code: Select all
select * from users where clue > 0; 0 rows returned.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 7
- Joined: Mon Oct 23, 2006 4:44 am