Problem in joining 2 tables
Moderators: chulett, rschirm, roy
Problem in joining 2 tables
I have 2 tables to join, in which the column names are not same and the condition is not simple. I have a condition like this:
where table1.a>table2.b and table1.a<=table2.c(in between condition). How can I do this in parallel job.
where table1.a>table2.b and table1.a<=table2.c(in between condition). How can I do this in parallel job.
Are the two tables in the same database? If so join in the database first.
If they are not it is more difficult. Are those the only two key fields or are there more key fields that you could join on but which would return multiple records for the same keys? If so you could join on the other keys passing through table1.a, table2.b and table3.c, which will give you X-product and then use a filter stage to restrict output to the records where your condition is true.
If they are not it is more difficult. Are those the only two key fields or are there more key fields that you could join on but which would return multiple records for the same keys? If so you could join on the other keys passing through table1.a, table2.b and table3.c, which will give you X-product and then use a filter stage to restrict output to the records where your condition is true.
Regards,
Nick.
Nick.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: Problem in joining 2 tables
You should not need a join condition at all and hence you won't need renaming columns. Achieve a cross join of the two tables and put a filter where you can specify the condition you mentioned.Amarpreet wrote:table1.a>table2.b and table1.a<=table2.c(in between condition)
It took me fifteen years to discover I had no talent for ETL, but I couldn't give it up because by that time I was too famous.
In this case if there is a possibility of more than one record satisfying the condition, please do not forget to set the link name from which multiple records can come (u can set this property by double clicking the lookup condition)kumar_s wrote:Or do a lookup to the table2 for a dummy column with a value say '1'. You can check the lookup condition as give by you table1.a>table2.b and table1.a<=table2.c.
It took me fifteen years to discover I had no talent for ETL, but I couldn't give it up because by that time I was too famous.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 1
- Joined: Thu Jun 07, 2007 12:40 pm
How exactly would you specify the lookup condition (I am new to DataStage). I need to generate a undetermined number of output rows per input. This thread seems to get me the closest but I am having issues reducing the output to the rows I am looking for.sud wrote:In this case if there is a possibility of more than one record satisfying the condition, please do not forget to set the link name from which multiple records can come (u can set this property by double clicking the lookup condition)kumar_s wrote:Or do a lookup to the table2 for a dummy column with a value say '1'. You can check the lookup condition as give by you table1.a>table2.b and table1.a<=table2.c.
Well, you can check out the lookup stage and use dummy columns in both primary and secondary lookup inputs to achieve a cross join and set lookup condition in the lookup stage.
But, what do you mean by "generate a undetermined number of output rows per input"![Question :?:](./images/smilies/icon_question.gif)
But, what do you mean by "generate a undetermined number of output rows per input"
![Question :?:](./images/smilies/icon_question.gif)
It took me fifteen years to discover I had no talent for ETL, but I couldn't give it up because by that time I was too famous.