Need Sortcard for filtering records using SYNCSORT

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?

sdsinfo · New User Joined: 14 Jan 2008 Posts: 10 Location: bangalore

use LeftOUTER JOIN

expat · Posted: Thu Jul 23, 2009 5:46 pm

dick scherrer · Posted: Thu Jul 23, 2009 9:05 pm

Hi Expat,

knn9413 · New User Joined: 23 Jul 2009 Posts: 17 Location: US

Aaru · Posted: Fri Jul 24, 2009 10:25 am

Knn,

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.

Ketan Varhade · Posted: Fri Jul 24, 2009 6:00 pm

Try with JOINUNPAIRED F2 and then try the above sort card

Alissa Margulies · Posted: Tue Jul 28, 2009 1:20 am

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:

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.

dick scherrer · Posted: Wed Jul 29, 2009 6:03 am

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

d