Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Need Sortcard for filtering records using SYNCSORT

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
G-Man

New User


Joined: 09 Oct 2008
Posts: 5
Location: Madras

PostPosted: Tue Jul 21, 2009 11:13 am    Post subject: Need Sortcard for filtering records using SYNCSORT
Reply with quote

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
View user's profile Send private message

sdsinfo

New User


Joined: 14 Jan 2008
Posts: 10
Location: bangalore

PostPosted: Thu Jul 23, 2009 5:26 pm    Post subject: hi
Reply with quote

use LeftOUTER JOIN
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Thu Jul 23, 2009 5:46 pm    Post subject: Re: hi
Reply with quote

sdsinfo wrote:
use LeftOUTER JOIN

Huh ??? Isn't that SQL.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Jul 23, 2009 9:05 pm    Post subject: Reply to: Need Sortcard for filtering records using SYNCSORT
Reply with quote

Hi Expat,

Quote:
use LeftOUTER JOIN
Possibly this should have been - after loading to 2 database tables, use LeftOUTER JOIN. . .

d
Back to top
View user's profile Send private message
knn9413

New User


Joined: 23 Jul 2009
Posts: 17
Location: US

PostPosted: Thu Jul 23, 2009 9:07 pm    Post subject: Re: Need Sortcard for filtering records using SYNCSORT
Reply with quote

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
View user's profile Send private message
Aaru

Senior Member


Joined: 03 Jul 2007
Posts: 1288
Location: Chennai, India

PostPosted: Fri Jul 24, 2009 10:25 am    Post subject: Reply to: Need Sortcard for filtering records using SYNCSORT
Reply with quote

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
View user's profile Send private message
G-Man

New User


Joined: 09 Oct 2008
Posts: 5
Location: Madras

PostPosted: Fri Jul 24, 2009 10:47 am    Post subject:
Reply with quote

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
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Fri Jul 24, 2009 6:00 pm    Post subject:
Reply with quote

Try with JOINUNPAIRED F2 and then try the above sort card
Back to top
View user's profile Send private message
Alissa Margulies

SYNCSORT Support


Joined: 25 Jul 2007
Posts: 500
Location: USA

PostPosted: Tue Jul 28, 2009 1:20 am    Post subject:
Reply with quote

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
View user's profile Send private message
G-Man

New User


Joined: 09 Oct 2008
Posts: 5
Location: Madras

PostPosted: Tue Jul 28, 2009 1:07 pm    Post subject: Reply to: Need Sortcard for filtering records using SYNCSORT
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Jul 29, 2009 6:03 am    Post subject: Reply to: Need Sortcard for filtering records using SYNCSORT
Reply with quote

Good to hear it is working - thanks for letting us know icon_smile.gif

d
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Syncsort Help to group fields sudhakarraju SYNCSORT 6 Thu Dec 29, 2016 1:38 am
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts syncsort: copy lines after the keyword shreya19 SYNCSORT 7 Fri Dec 02, 2016 9:47 am
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts Syncsort - Hard coded Value in output PORYES SYNCSORT 1 Thu Nov 03, 2016 9:23 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us