View previous topic :: View next topic
Author
Message
G-Man New User Joined: 09 Oct 2008Posts: 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 2008Posts: 10 Location: bangalore
use LeftOUTER JOIN
Back to top
expat Global Moderator Joined: 14 Mar 2007Posts: 8797 Location: Welsh Wales
sdsinfo wrote:
use LeftOUTER JOIN
Huh ??? Isn't that SQL.
Back to top
dick scherrer Moderator Emeritus Joined: 23 Nov 2006Posts: 19244 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 2009Posts: 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 2007Posts: 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 2008Posts: 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 2009Posts: 197 Location: Mumbai
Try with JOINUNPAIRED F2 and then try the above sort card
Back to top
Alissa Margulies SYNCSORT Support Joined: 25 Jul 2007Posts: 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 2008Posts: 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 2006Posts: 19244 Location: Inside the Matrix
Good to hear it is working - thanks for letting us know
d
Back to top
Please enable JavaScript!