Synsort shows on the same outfile as the joined records

ken herbolic · New User Joined: 24 Jan 2011 Posts: 16 Location: buffalo

i have two files to join. But I what the unjoined records to show on the same outfile as the joined records. I have used join before but have not done needed this type of output..... any done this or can you help?

dick scherrer · Posted: Sat May 21, 2011 10:00 am

Hello,

Show a bit of sample input from both files that contains some matches and some non-matches and the output you want when this sample data is processed.

Mention the recfm and lrecl of all files.

Anuj Dhawan · Posted: Mon May 23, 2011 4:03 pm

ken herbolic · New User Joined: 24 Jan 2011 Posts: 16 Location: buffalo

Hi:
The input J1 records has an account number in bytes 1 to 9

The input file J2 has an account number also in bytes 1 to 9

Matched output will be done on account number

Non matched output will be accounts numbers that do not matched from J1 to J2.

The data that is in bytes 10 to 90 I know how to do the reformatting.

LRECL on both files is 90.

Thank you,
Ken

gylbharat · Posted: Mon May 23, 2011 6:41 pm

Hi Ken,

Can you please let us know what is the output that needs to be written to the output file? Do you need data from file 1 or file 2?

It would be better if you can explain the requirement with some sample data?

ken herbolic · New User Joined: 24 Jan 2011 Posts: 16 Location: buffalo

Hi:
I need all the data from both J1 an J2.
All account number will be on J1 but some could be missing on J2.
The first 36 bytes of J1 will be displayed first on the output file then bytes 10 to 45 of J2 display on the same output line os J1 right after it.

make sense

daveporcelan · Posted: Mon May 23, 2011 7:21 pm

Try using - JOIN UNPARED,F1

That along with what you already know should do it for you.

ken herbolic · New User Joined: 24 Jan 2011 Posts: 16 Location: buffalo

thank you !!!!

I will give it a go

ken herbolic · New User Joined: 24 Jan 2011 Posts: 16 Location: buffalo

That worked but i now have two account # columns. I would like to show just one account # column. do you know how I could get the account numbers in account column 2 to display in account column 1 then eliminate account column 2.... here is a smaple of the output I have now and the code used to get this far:

Accout # Account #
000000001 001
000000002 002
000010359 258 000010359 258
000010502 2 000010502 2
000010570 11 000010570 11
000010588 90 000010588 90
000010596 2 000010596 2
000023868 15017 000023868 14814

Code:
//SYSIN DD *
JOINKEYS FILE=F1,FIELDS=(1,9,A)
JOINKEYS FILE=F2,FIELDS=(1,9,A)
JOIN UNPAIRED,F1,F2
REFORMAT FIELDS=(F1:1,9,28,11,F2:1,9,45,08)
SORT FIELDS=COPY

ken herbolic · New User Joined: 24 Jan 2011 Posts: 16 Location: buffalo

Sorry that output did not format correctly here is another try:

Account # Account #
000000001 001
000000002 002
000010359 258 000010359 258
000010502 2 000010502 2
000010570 11 000010570 11
000010588 90 000010588 90
000010596 2 000010596 2
000023868 15017 000023868 14814

daveporcelan · Posted: Mon May 23, 2011 8:40 pm

Above you said:

ken herbolic · New User Joined: 24 Jan 2011 Posts: 16 Location: buffalo

sorry cannot get the spacing to stay once submitted

daveporcelan · Posted: Mon May 23, 2011 8:41 pm

Click the Code button

Paste in you data

Click the Code button

ken herbolic · New User Joined: 24 Jan 2011 Posts: 16 Location: buffalo

I used dashes to format the data .... As i metioned is there anyway to get the account numbers form F2 to display in the F1 column...then delete the account #s in the F2 column

daveporcelan · Posted: Mon May 23, 2011 8:52 pm

Did you read my suggestions above?

ken herbolic · New User Joined: 24 Jan 2011 Posts: 16 Location: buffalo

ken herbolic · New User Joined: 24 Jan 2011 Posts: 16 Location: buffalo

Yes ...just tried that now and it is much better thanks!!!..... the output now displays records on F1 that are not on F2 and I have one account number column......Now the only thing left missing is there are records on F2 that are not on F1. These records are not displaying on the output and I need them too. I need to display all records from both files, matched on one line and unmatched on another as the code is doing now for F1.

Is this possiable to do ?

daveporcelan · Posted: Mon May 23, 2011 9:12 pm

Earlier you said

ken herbolic · New User Joined: 24 Jan 2011 Posts: 16 Location: buffalo

thank you...

ken herbolic · New User Joined: 24 Jan 2011 Posts: 16 Location: buffalo

Hi.... can anyone else help on this request...I searched but cannot find the requirement I need. Again I have two files. I need to display the account number from both files just once. I need to display all records from both files, matched and unmatched. this is want has been suggested so far and is almost there:
JOINKEYS FILE=F1,FIELDS=(1,9,A)
JOINKEYS FILE=F2,FIELDS=(1,9,A)
JOIN UNPAIRED
REFORMAT FIELDS=(F1:1,9,28,11,F2:45,08)
SORT FIELDS=COPY

Here is the output...notice no account number displays for the F2 record.

DB2 Guy · New User Joined: 28 Oct 2008 Posts: 98 Location: Cubicle

It would be nice if you show us some sample input records frm both the file and the expected output from that.

ken herbolic · New User Joined: 24 Jan 2011 Posts: 16 Location: buffalo

thanks for all the help....sync sort support has shown me the problem with my code....the below is a working sample for anyone that may have a simiplar need.