View previous topic :: View next topic
|
Author |
Message |
gabriel.ryoga
New User
Joined: 07 Jun 2007 Posts: 31 Location: Spain
|
|
|
|
File 1
1 a
1 b
2 d
2 e
File 2
1 x
1 y
2 z
3 w
Output:
1 a x
1 a y
1 b x
1 b y
2 d z
2 e z
The problem is that I need to match every register of file 1 with all the registers of file 2, like a INNER JOIN in a SQL QUERY.
Can somebody help me?? ^_^ |
|
Back to top |
|
|
krisprems
Active Member
Joined: 27 Nov 2006 Posts: 649 Location: India
|
|
Back to top |
|
|
gabriel.ryoga
New User
Joined: 07 Jun 2007 Posts: 31 Location: Spain
|
|
|
|
Thanks for the answers...., it seems that I will have to make some program if I want to do the cartesian join.
Thanks for the help!
^_^ |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
I do not believe you need a full cartesian product. If i understand, you want to match on the first value in each file and then write out all of the existing combinations for that "match".
How close to what you've posted is the actual data? How many records will be in the first 2 files? Is the "3 w" record supposed to be omitted in the output file? |
|
Back to top |
|
|
gabriel.ryoga
New User
Joined: 07 Jun 2007 Posts: 31 Location: Spain
|
|
|
|
I don't need a full cartesian product, just a cartesian product between the records with the same key. If some records don't match they have to be omitted
The files will have lots of records maybe 7 million or more, so I need some utility with enough power to do that without spending all the day. |
|
Back to top |
|
|
banand
New User
Joined: 05 Jun 2007 Posts: 28 Location: Mumbai
|
|
|
|
Hi,
No need for the program to do this.
You can do it thru Synsort or DFsort using the joinkey.
Joinkeys files=F1,Fields=(1,1,A)
Joinkeys files=F2,Fields=(1,1,A)
Join unpaired,F1
Reformat Fields=(F1:1,2,F2:,2,1)
Sort fields=(1,1,CH,A)
If you want all the records in the file1, even match is not found
use the unpaired option. Else if you only matched records remove Join unpaired option.
Thanks |
|
Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
|
|
|
|
Quote: |
You can do it thru Synsort or DFsort using the joinkey. |
DFSORT does NOT support that function. |
|
Back to top |
|
|
banand
New User
Joined: 05 Jun 2007 Posts: 28 Location: Mumbai
|
|
|
|
Hi,
Can you clear me whether DFSORT and ICEMAN are the same one ?.
IF yes , then DFSORT is supporting the Joinkey fuction. Because I have
tried with ICEMAN, it is working fine.
Thanks, |
|
Back to top |
|
|
gabriel.ryoga
New User
Joined: 07 Jun 2007 Posts: 31 Location: Spain
|
|
|
|
I've tried with this:
//STEP0001 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTJNF1 DD *
123 AAAAAA
234 BBBBBBBB
345 CC
//SORTJNF2 DD *
123 JJJJJJJJJJJJ RRRRRRRR
123 BBBBBBBB GGGGG
123 CC KKKKKKKKKK
234 JJJJJJJJJJJ RRRRRRRR
234 BBBBBBBB GGGGG
//SORTOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(01,3,A)
JOINKEYS FILES=F2,FIELDS=(01,3,A)
REFORMAT FIELDS=(F1:1,40,F2:10,60)
//*
and this is the result:
********************************* TOP OF DATA **********************************
ICE000I 1 - CONTROL STATEMENTS FOR 5694-A01, Z/OS DFSORT V1R5 - 15:17 ON FRI JUN
JOINKEYS FILES=F1,FIELDS=(01,3,A)
$
ICE005A 0 STATEMENT DEFINER ERROR
JOINKEYS FILES=F2,FIELDS=(01,3,A)
$
ICE005A 0 STATEMENT DEFINER ERROR
REFORMAT FIELDS=(F1:1,40,F2:10,60)
$
ICE005A 0 STATEMENT DEFINER ERROR
ICE010A 0 NO SORT OR MERGE CONTROL STATEMENT
ICE751I 0 C5-K90007 C6-K90007 E7-K11698
ICE026I 1 SMF RECORD NOT WRITTEN TO THE SMF DATA SET(RC=20)
ICE052I 3 END OF DFSORT
******************************** BOTTOM OF DATA ******************************** |
|
Back to top |
|
|
murmohk1
Senior Member
Joined: 29 Jun 2006 Posts: 1436 Location: Bangalore,India
|
|
|
|
gabriel.ryoga,
As Frank told DFSORT doesn't support join function.
Quote: |
ICE005A 0 STATEMENT DEFINER ERROR |
Explanation for ICE005A:
Explanation: Critical. A control statement did not contain one of the
acceptable operation definers (ALTSEQ, DEBUG, END, INCLUDE, INREC, MERGE,
MODS, OMIT, OPTION, OUTFIL, OUTREC, RECORD, SORT, SUM). You can also
receive this message for continuation lines after a line that has an
error.
Quote: |
ICE010A 0 NO SORT OR MERGE CONTROL STATEMENT |
In the sort control card, you have missed out basic sort statement (sort fields=copy/option copy). |
|
Back to top |
|
|
banand
New User
Joined: 05 Jun 2007 Posts: 28 Location: Mumbai
|
|
|
|
Hi gabriel,
You have missed the SORT STATEMENT, Please include the SORT STATEMENT in the SYSIN.And try again
Thanks |
|
Back to top |
|
|
gabriel.ryoga
New User
Joined: 07 Jun 2007 Posts: 31 Location: Spain
|
|
|
|
I tried with the sort stament too, and I got the same answer..... |
|
Back to top |
|
|
murmohk1
Senior Member
Joined: 29 Jun 2006 Posts: 1436 Location: Bangalore,India
|
|
|
|
Quote: |
I tried with the sort stament too, and I got the same answer..... |
It could be because of join statement. |
|
Back to top |
|
|
gabriel.ryoga
New User
Joined: 07 Jun 2007 Posts: 31 Location: Spain
|
|
|
|
It seems that's no way to do this in a JCL step...... |
|
Back to top |
|
|
banand
New User
Joined: 05 Jun 2007 Posts: 28 Location: Mumbai
|
|
|
|
Hi gabriel,
Can you paste the JCL which have used and ERROR message you got.
Because if you have properly added the SORT statement, you wont get the same error message.
Thanks |
|
Back to top |
|
|
banand
New User
Joined: 05 Jun 2007 Posts: 28 Location: Mumbai
|
|
|
|
Hi,
Can any one clear me whether DFSORT and ICEMAN are the same one ?.
Thanks, |
|
Back to top |
|
|
murmohk1
Senior Member
Joined: 29 Jun 2006 Posts: 1436 Location: Bangalore,India
|
|
Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
|
|
|
|
Quote: |
You have missed the SORT STATEMENT, Please include the SORT STATEMENT in the SYSIN.And try again |
DFSORT does NOT support JOINKEYS. You can't make it support JOINKEYS by adding a SORT statement or any other statement.
Quote: |
Can you clear me whether DFSORT and ICEMAN are the same one ?.
IF yes , then DFSORT is supporting the Joinkey fuction. Because I have
tried with ICEMAN, it is working fine. |
PGM=ICEMAN will invoke the sort product at your site which can be DFSORT, Syncsort or CA-Sort. If JOINKEYS is working, then the sort product at your site is Syncsort, not DFSORT. |
|
Back to top |
|
|
banand
New User
Joined: 05 Jun 2007 Posts: 28 Location: Mumbai
|
|
|
|
Thanks for the Clarification. |
|
Back to top |
|
|
i413678 Currently Banned Active User
Joined: 19 Feb 2005 Posts: 112 Location: chennai
|
|
|
|
Hi,
I had given SORT STATEMENT also....But, I am getting the same problem...
Regards,
pavan |
|
Back to top |
|
|
gabriel.ryoga
New User
Joined: 07 Jun 2007 Posts: 31 Location: Spain
|
|
|
|
I finally did a cobol program to do the cartesian product.
Thank's to everybody for the help.
Bye. |
|
Back to top |
|
|
Frank Yaeger
DFSORT Developer
Joined: 15 Feb 2005 Posts: 7129 Location: San Jose, CA
|
|
Back to top |
|
|
|