Using Joinkeys in Syncsort

Deepan Raj · New User Joined: 30 Dec 2009 Posts: 9 Location: India

Hi,

Can anyone tell me how to do this using Joinkeys in Syncsort. there are two files F1 and F2. both files are compared with their keys, if the key matches then i have to inserted a mark, say 'D' at the end of the record else space in the file F1.

I tried joinkeys and outrec, but not able get the actual result

expat · Posted: Mon Jan 04, 2010 5:07 pm

Unless you post the code that you are using, along with an explanation of what is not happening, the release level of the product, the RECFM & LRECL of the two files, the position, length and format of the key fields, there isn't too much people can say to help you.

Deepan Raj · New User Joined: 30 Dec 2009 Posts: 9 Location: India

File F1 - LRECL = 100 and KEY length is 23, position 1
File F2 - LRECL = 100 and KEY length is 23, position 14

i need to compare the key in both files and if it matches then i need to insert a mark for instance 'D' in the file F1.

I tried joinkeys - syncsort

JOINKEYS FILE=F1,
FIELDS=(1,23,A)
JOINKEYS FILE=F2,
FIELDS=(14,23,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,100)
INREC FIELDS=(1:1,100,101:C'D')
SORT FIELDS=(1,4,CH,A)

i need to insert 'D' only in the matched record. this code inserting 'D' in both matched and unmatched record in File F1.

sril.krishy · Posted: Mon Jan 04, 2010 11:28 pm

Hi,
Remove the JOIN UNPAIRED,F1 and do the outrec.Here is the simple example.

Deepan Raj · New User Joined: 30 Dec 2009 Posts: 9 Location: India

Thanks Krishy,

By this code i will get only matched records with mark 'D' in the output.
But in my case, I want both matched and unmatched records, with mark 'D' only in matched records in the output file.

Thanks,
Deepan

Deepan Raj · New User Joined: 30 Dec 2009 Posts: 9 Location: India

Krishy,

I want output as

123 A D
234 B

sril.krishy · Posted: Tue Jan 05, 2010 5:30 pm

Hi,
Try this.

Deepan Raj · New User Joined: 30 Dec 2009 Posts: 9 Location: India

Hi Krishy,

Yours working fine. Thanks a lot.
Since this sort the file two times, i tried this

JOINKEYS FILE=F1,
FIELDS=(1,4,A)
JOINKEYS FILE=F2,
FIELDS=(1,4,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,20,F2:1,20)
SORT FIELDS=COPY
OUTREC IFTHEN=(WHEN=(21,4,CH,NE,C' '),
BUILD=(1:1,10,11:C'D',12:11,10)),
IFTHEN=(WHEN=NONE,
BUILD=(1:1,10,11:C' ',12:11,10))

This also doing the same work..
Thanks for ur idea..[/img]

l.nethaji · Posted: Fri Jan 08, 2010 11:44 pm

Hi,

//CTL1CNTL DD *
SORT FIELDS=COPY
JOINKEYS FILES=F1,FIELDS=(1,3,A)
JOINKEYS FILES=F2,FIELDS=(1,3,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,6,F2:1,6)

Based on the first condition we are going to get the output as

123 A
234 B

Can any one explain me how this condition works using the above o/p from first .

CTL2CNTL DD *
OUTREC IFTHEN=(WHEN=(8,2,CH,NE,C' '),
BUILD=(1,6,C'D')),
IFTHEN=(WHEN=NONE,
BUILD=(1,6))
SORT FIELDS=COPY

Pls help

dick scherrer · Posted: Sat Jan 09, 2010 1:40 am

Hello,

What happens when you run it?

Deepan Raj · New User Joined: 30 Dec 2009 Posts: 9 Location: India

Hi Nethaji,

//CTL1CNTL DD *
SORT FIELDS=COPY
JOINKEYS FILES=F1,FIELDS=(1,3,A)
JOINKEYS FILES=F2,FIELDS=(1,3,A)
JOIN UNPAIRED,F1
REFORMAT FIELDS=(F1:1,6,F2:1,6)

Based on the first condition u will get output as

123 A123 A
234 B

CTL2CNTL DD *
OUTREC IFTHEN=(WHEN=(8,2,CH,NE,C' '),
BUILD=(1,6,C'D')),
IFTHEN=(WHEN=NONE,
BUILD=(1,6))
SORT FIELDS=COPY

Based on second condition u will get output as

123 AD
234 B

watz ur output???