|
View previous topic :: View next topic
|
| Author |
Message |
G-Man
New User
Joined: 09 Oct 2008
Posts: 5
Location: Madras
|
|
|
|
Hi,
Please find the details of my requirment.
Input-1
Key (20byte) First name(20) Last name(20)
XXX01M--------------$$$$----------------####----------------
XXX01M001000051-----LOGIST--------------LENIN---------------
XXX01M001000054-----CHIAG---------------JACKY---------------
XXX02M--------------####------------------------------------
XXX02M----------------------------------@@@@----------------
XXX02M633533435-----KELLY---------------JOHNSON-------------
XXX02M633533436-----CRAIG---------------KINDS---------------
Input-2
Key(20byte)
XXX01X001000054
XXX02M633533436
Required Output:
XXX01M--------------$$$$----------------####----------------
XXX01M001000054-----CHIAG---------------JACKY---------------
XXX02M--------------####------------------------------------
XXX02M----------------------------------@@@@----------------
XXX02M633533436-----CRAIG---------------KINDS---------------
This input has two key parts (Key1 – 6byte, key2 – 14byte). If the (key1+key2) from input-2 is present in input-1, I need that particular record along with the records with key1 value from input-1 file.
Can I do this in a single sort step using JOIN? |
|
| Back to top |
|
 |
sdsinfo
New User
Joined: 14 Jan 2008
Posts: 10
Location: bangalore
|
|
|
|
| use LeftOUTER JOIN |
|
| Back to top |
|
|
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
|
|
|
|
| sdsinfo wrote: |
| use LeftOUTER JOIN |
Huh ??? Isn't that SQL. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hi Expat,
| Quote: |
| use LeftOUTER JOIN |
Possibly this should have been - after loading to 2 database tables, use LeftOUTER JOIN. . .
d |
|
| Back to top |
|
|
knn9413
New User
Joined: 23 Jul 2009
Posts: 17
Location: US
|
|
|
|
| G-Man wrote: |
Hi,
Please find the details of my requirment.
Input-1
Key (20byte) First name(20) Last name(20)
XXX01M--------------$$$$----------------####----------------
XXX01M001000051-----LOGIST--------------LENIN---------------
XXX01M001000054-----CHIAG---------------JACKY---------------
XXX02M--------------####------------------------------------
XXX02M----------------------------------@@@@----------------
XXX02M633533435-----KELLY---------------JOHNSON-------------
XXX02M633533436-----CRAIG---------------KINDS---------------
Input-2
Key(20byte)
XXX01X001000054
XXX02M633533436
Required Output:
XXX01M--------------$$$$----------------####----------------
XXX01M001000054-----CHIAG---------------JACKY---------------
XXX02M--------------####------------------------------------
XXX02M----------------------------------@@@@----------------
XXX02M633533436-----CRAIG---------------KINDS---------------
This input has two key parts (Key1 – 6byte, key2 – 14byte). If the (key1+key2) from input-2 is present in input-1, I need that particular record along with the records with key1 value from input-1 file.
Can I do this in a single sort step using JOIN? |
Did you even try going thru the Syncsort manual for this.. if you did then you would have found that there is something called JOINKEYS... now if you could go and read and try it out, you should be able to do it |
|
| Back to top |
|
|
Aaru
Senior Member
Joined: 03 Jul 2007
Posts: 1287
Location: Chennai, India
|
|
|
|
Knn,
| Quote: |
| Did you even try going thru the Syncsort manual for this.. if you did then you would have found that there is something called JOINKEYS... now if you could go and read and try it out, you should be able to do it |
AFAIK this forum has no SYNCSORT manuals.
If you need it you should mail or PM Alissa. |
|
| Back to top |
|
|
G-Man
New User
Joined: 09 Oct 2008
Posts: 5
Location: Madras
|
|
|
|
Hi Knn,
I have already tried JOINKEYS.
JOINKEYS FILES=F1,FIELDS=(1,20,A)
JOINKEYS FILES=F2,FIELDS=(1,20,A)
SORT FIELDS=COPY
REFORMAT FIELDS=(F1:1,60)
This was the card I used. But using this I could only get
XXX01M001000054-----CHIAG---------------JACKY---------------
XXX02M633533436-----CRAIG---------------KINDS---------------
But I want the records of XXX01M and XXX02M also.
Is there any possiblity that I can give another key in the JOINKEY statement with first 6 bytes of the file as key and 14 bytes as spaces? By that way I can being XXX01M and XXX02M records also.
Also I cannot use Database. Please Advise me. |
|
| Back to top |
|
|
Ketan Varhade
Active User
Joined: 29 Jun 2009
Posts: 197
Location: Mumbai
|
|
|
|
| Try with JOINUNPAIRED F2 and then try the above sort card |
|
| Back to top |
|
|
Alissa Margulies
SYNCSORT Support
Joined: 25 Jul 2007
Posts: 496
Location: USA
|
|
|
|
G-Man,
Assuming that the XXX01X in your second input file is just a typo, and it is really XXX01M, and all the dashes (-) are blanks, then here is a SyncSort for z/OS job that will do what you asked:
| Code: |
//STEP1 EXEC PGM=SORT
//SORTJNF1 DD *
XXX01M $$$$ ####
XXX01M001000051 LOGIST LENIN
XXX01M001000054 CHIAG JACKY
XXX02M ####
XXX02M @@@@
XXX02M633533435 KELLY JOHNSON
XXX02M633533436 CRAIG KINDS
//SORTJNF2 DD *
XXX01M001000054
XXX02M633533436
//SORTOUT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//SYSIN DD *
JOINKEYS FILES=F1,FIELDS=(1,20,A)
JOINKEYS FILES=F2,FIELDS=(1,20,A)
REFORMAT FIELDS=(F1:1,60,F2:1,20)
JOIN UNPAIRED,F1
SORT FIELDS=COPY
OUTFIL INCLUDE=(1,20,CH,EQ,61,20,CH,OR,7,14,CH,EQ,C' '),
BUILD=(1,60)
/* |
|
|
| Back to top |
|
|
G-Man
New User
Joined: 09 Oct 2008
Posts: 5
Location: Madras
|
|
|
|
Hi,
Thanx Alissa for the reply. Your assumptions were right.
Actually the sample data I used didn't fully explain my requirement.
I will try again now.
Input-1
Key (20byte) First name(20) Last name(20)
XXX01M--------------$$$$----------------####----------------
XXX01M001000051-----LOGIST--------------LENIN---------------
XXX01M001000054-----CHIAG---------------JACKY---------------
XXX02M--------------####------------------------------------
XXX02M----------------------------------@@@@----------------
XXX02M633533435-----KELLY---------------JOHNSON-------------
XXX02M633533436-----CRAIG---------------KINDS---------------
XXX03M--------------****------------------------------------
XXX04M--------------****------------------------------------
Input-2
Key(20byte)
XXX01X001000054
XXX02M633533436
Required Output:
XXX01M--------------$$$$----------------####----------------
XXX01M001000054-----CHIAG---------------JACKY---------------
XXX02M--------------####------------------------------------
XXX02M----------------------------------@@@@----------------
XXX02M633533436-----CRAIG---------------KINDS---------------
I need to ignore XXX03M and XXX04M from output since the input-2 didn't have any record starting with XXX03M and XXX04M.
I made minor modification to your sortcard like this:
JOINKEYS FILES=F1,FIELDS=(1,6,A)
JOINKEYS FILES=F2,FIELDS=(1,6,A)
REFORMAT FIELDS=(F1:1,60,F2:1,20)
JOIN UNPAIRED,F1
SORT FIELDS=COPY
OUTFIL INCLUDE=((1,6,CH,EQ,61,6,CH,AND,7,14,CH,EQ,C' '),OR,1,20,CH,
EQ,61,20,CH),
BUILD=(1,60)
It worked fine.
Thank you once again. Thanx everybody for helping me.
Regards,
GurU. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Good to hear it is working - thanks for letting us know 
d |
|
| Back to top |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
