Catesian Product Of Sequential files
Moderators: chulett, rschirm, roy
Catesian Product Of Sequential files
Hi,
Our business Requirement states that we need to create cartesian product of 2 Sequentials files for loading.
For Example,
File Src1 Contains
Coulmn Names--> a b c
1 2 3
4 5 6
File Src2 Contains
Columns names ---->d e f
11 12 13
14 15 16
Then the output file shld contains
Column Names a b c d e f
1 2 3 11 12 13
1 2 3 14 15 16
4 5 6 11 12 13
4 5 6 14 15 16
Is there any work around without using Basic Routines and UV Stages.
Thanks in Advance.
Gokul
Our business Requirement states that we need to create cartesian product of 2 Sequentials files for loading.
For Example,
File Src1 Contains
Coulmn Names--> a b c
1 2 3
4 5 6
File Src2 Contains
Columns names ---->d e f
11 12 13
14 15 16
Then the output file shld contains
Column Names a b c d e f
1 2 3 11 12 13
1 2 3 14 15 16
4 5 6 11 12 13
4 5 6 14 15 16
Is there any work around without using Basic Routines and UV Stages.
Thanks in Advance.
Gokul
No, there is no simple point-and-click way, as you need to buffer the contents across rows. It is not difficult to do so with a Hash stage or program, though.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 145
- Joined: Fri May 02, 2003 9:59 am
- Location: Seattle, Washington. USA
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Charter Member
- Posts: 130
- Joined: Mon Sep 06, 2004 3:05 am
- Location: Dubai,UAE
Catesian Product Of Sequential files
Hi Gokul,
I agree with Sainath.Srinivasan suggestion and find it the easiest way to get the result.
Have u added a dummy column with the same value in both the Files?
For example in your case
File Src1 Contains
Coulmn Names--> dummy a b c
X 1 2 3
X 4 5 6
File Src2 Contains
Columns names ---->dummy d e f
X 11 12 13
X 14 15 16
X is the value of the dummy column. Now you just need to do an Inner join on the files with the dummy column as the key and you will get the desired result as I have achieved the same in my sample job.
I agree with Sainath.Srinivasan suggestion and find it the easiest way to get the result.
Have u added a dummy column with the same value in both the Files?
For example in your case
File Src1 Contains
Coulmn Names--> dummy a b c
X 1 2 3
X 4 5 6
File Src2 Contains
Columns names ---->dummy d e f
X 11 12 13
X 14 15 16
X is the value of the dummy column. Now you just need to do an Inner join on the files with the dummy column as the key and you will get the desired result as I have achieved the same in my sample job.
Warm Regards,
Amruta Bandekar
<b>If A equals success, then the formula is: A = X + Y + Z, X is work. Y is play. Z is keep your mouth shut. </b>
--Albert Einstein
Amruta Bandekar
<b>If A equals success, then the formula is: A = X + Y + Z, X is work. Y is play. Z is keep your mouth shut. </b>
--Albert Einstein
-
- Participant
- Posts: 64
- Joined: Fri Jul 16, 2004 7:53 am
-
- Charter Member
- Posts: 130
- Joined: Mon Sep 06, 2004 3:05 am
- Location: Dubai,UAE
Catesian Product Of Sequential files
Yes the Merge Stage
Warm Regards,
Amruta Bandekar
<b>If A equals success, then the formula is: A = X + Y + Z, X is work. Y is play. Z is keep your mouth shut. </b>
--Albert Einstein
Amruta Bandekar
<b>If A equals success, then the formula is: A = X + Y + Z, X is work. Y is play. Z is keep your mouth shut. </b>
--Albert Einstein
-
- Participant
- Posts: 64
- Joined: Fri Jul 16, 2004 7:53 am
Hi,
I tried using the Merge Stage.. the job got complied successfully.. i getting the following error while running the job...
Error from the Director:
DataStage Job 3 Phantom 3364
Program "DSP.Open": Line 122, Exception raised in GCI subroutine:
Access violation.
Attempting to Cleanup after ABORT raised in stage CartesianResult..CartesianJoin
DataStage Phantom Aborting with @ABORT.CODE = 3
I had problem with this stage earlier also i posted my problem earlier also but couldn't find the reason.. can some one help me in resolving this issue...
Thanks
Sudharsanan
I tried using the Merge Stage.. the job got complied successfully.. i getting the following error while running the job...
Error from the Director:
DataStage Job 3 Phantom 3364
Program "DSP.Open": Line 122, Exception raised in GCI subroutine:
Access violation.
Attempting to Cleanup after ABORT raised in stage CartesianResult..CartesianJoin
DataStage Phantom Aborting with @ABORT.CODE = 3
I had problem with this stage earlier also i posted my problem earlier also but couldn't find the reason.. can some one help me in resolving this issue...
Thanks
Sudharsanan
-
- Participant
- Posts: 64
- Joined: Fri Jul 16, 2004 7:53 am
-
- Charter Member
- Posts: 130
- Joined: Mon Sep 06, 2004 3:05 am
- Location: Dubai,UAE
Catesian Product Of Sequential files
Hi,
In a database table you do not need a dummy column, you can achieve the same result using CROSS JOIN.
In a database table you do not need a dummy column, you can achieve the same result using CROSS JOIN.
Warm Regards,
Amruta Bandekar
<b>If A equals success, then the formula is: A = X + Y + Z, X is work. Y is play. Z is keep your mouth shut. </b>
--Albert Einstein
Amruta Bandekar
<b>If A equals success, then the formula is: A = X + Y + Z, X is work. Y is play. Z is keep your mouth shut. </b>
--Albert Einstein
-
- Participant
- Posts: 64
- Joined: Fri Jul 16, 2004 7:53 am
Hi,
I used Merger stage as suggested using a dummy column and tried with complete set and inner join.But i was not able to get the desired result.
File Src1
Column list----->a b dummmy
1 2 Z
3 4 Z
5 6 Z
File Src2
Column list------->d e Dummy
11 12 Z
13 14 Z
15 16 Z
The OUptut was
a b d e Dummy Dummy1
1 2 11 12 Z Z
1 2 13 14 Z Z
1 2 15 16 Z Z
3 4 11 12 Z Z
1 2 11 12 Z Z
1 2 13 14 Z Z
5 6 11 12 Z Z
Can anyone tell me why the merger is behaving in such way?
I used Merger stage as suggested using a dummy column and tried with complete set and inner join.But i was not able to get the desired result.
File Src1
Column list----->a b dummmy
1 2 Z
3 4 Z
5 6 Z
File Src2
Column list------->d e Dummy
11 12 Z
13 14 Z
15 16 Z
The OUptut was
a b d e Dummy Dummy1
1 2 11 12 Z Z
1 2 13 14 Z Z
1 2 15 16 Z Z
3 4 11 12 Z Z
1 2 11 12 Z Z
1 2 13 14 Z Z
5 6 11 12 Z Z
Can anyone tell me why the merger is behaving in such way?
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Participant
- Posts: 108
- Joined: Wed Jul 28, 2004 7:15 am