Catesian Product Of Sequential files

Gokul · Post by **Gokul** » Mon May 02, 2005 8:48 am

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

ArndW · Post by **ArndW** » Mon May 02, 2005 9:06 am

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.

shawn_ramsey · Post by **shawn_ramsey** » Mon May 02, 2005 10:37 am

I would just load them up into temporary tables on the DBMS and do the cartesian using the database.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Mon May 02, 2005 12:32 pm

Set a dummy constant column on both files and perform a join between them using a join stage.

Alternatively use Unix commands to achieve the desired result.

Gokul · Post by **Gokul** » Tue May 03, 2005 3:32 am

Hi,

i tries using a dummy column and then performed the join on it.
But still i have not achieved the desired result.
Since Hash file behave like Tables.
Cannot we do cartesian Product on hash as on tables using the selection query like select Src1.a,Src2.b,... from Src1,Src2.

Sunshine2323 · Post by **Sunshine2323** » Tue May 03, 2005 4:47 am

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.

sudharsanan · Post by **sudharsanan** » Wed May 04, 2005 12:58 am

Hi Sunshine,

Can you tell us which stage to be used in Server jobs to obtain the desired result.. can i use a Merge Stage to do the join between the sequential files...can you please tell me how to achieve the result?..

Thanks
Sudharsanan

Sunshine2323 · Post by **Sunshine2323** » Wed May 04, 2005 1:05 am

Yes the Merge Stage

sudharsanan · Post by **sudharsanan** » Wed May 04, 2005 1:20 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

sudharsanan · Post by **sudharsanan** » Wed May 04, 2005 2:44 am

Hi All,

As I couldn't use the Merge Stage..I used the Database approach where i moved the data to two temporary tables did a simple select with the table and got the result very easily... Thanks for all the inputs...

Regards
Sudharsanan

Sunshine2323 · Post by **Sunshine2323** » Wed May 04, 2005 3:47 am

Hi,

In a database table you do not need a dummy column, you can achieve the same result using CROSS JOIN.

sudharsanan · Post by **sudharsanan** » Wed May 04, 2005 3:48 am

I did the same way as you said..............

Gokul · Post by **Gokul** » Wed May 04, 2005 5:35 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?

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed May 04, 2005 5:52 am

Is the result the full list you obtained or only partial? The column layout may be affected by the mapping work. Can you create 2 test file for your scenaria and check the merge stage alone before you include the rest.

Prashantoncyber · Post by **Prashantoncyber** » Wed May 04, 2005 5:56 am

I guess you have to retry carefully once again to get the desired result.