IBM Mainframe Forum

by **Cobbhill** » Thu Apr 10, 2014 7:40 pm

I have a join that I built that runs fine if I put the results to 3 files.

SORT FIELDS=COPY
JOINKEYS FILE=F1,FIELDS=(01,16,A)
JOINKEYS FILE=F2,FIELDS=(22,16,A)
JOIN UNPAIRED F1,F2 <== NON MATCHES FROM BOTH FILES
REFORMAT FIELDS=(F1:01,80,F2:1,320,?)
OUTFIL FNAMES=SORTOF01,INCLUDE=(401,1,CH,EQ,C'B'), <==MATCH
BUILD=(38,9,12X,102,16,118,283)
OUTFIL FNAMES=SORTOF02,INCLUDE=(401,1,CH,EQ,C'1'),
BUILD=(38,9,1X,1,16,1X,C'GROUP NOT FOUND',38X) <== NO GROUP
OUTFIL FNAMES=SORTOF03,INCLUDE=(401,1,CH,EQ,C'2'),
BUILD=(C'000000000',1X,102,16,1X,C'NO CUSTOMER FOR GROUP',32X)

What I have been trying to do is put logic in to combine SORTOF02 AND SORTOF03 to one file. I know I can just add another sort to do this but thought I would try with ifthen when conditions.

Select all

SORT FIELDS=COPY
JOINKEYS FILE=F1,FIELDS=(01,16,A)
JOINKEYS FILE=F2,FIELDS=(22,16,A)
JOIN UNPAIRED F1,F2 <== NON MATCHES FROM BOTH FILES
REFORMAT FIELDS=(F1:01,80,F2:1,320,?)
OUTFIL FNAMES=SORTOF01,INCLUDE=(401,1,CH,EQ,C'B'), <==MATCH
BUILD=(38,9,12X,102,16,118,283)
OUTFIL FNAMES=SORTOF02,INCLUDE=(401,1,CH,NE,C'B'),
IFTHEN=(WHEN=(103,1,CH,GT,C' '),
BUILD=(C'000000000 ',102,16,1X,C'NO CUSTOMER FOR GROUP',32X)),
IFTHEN=(WHEN=(38,1,CH,GT,C' '),
BUILD=(38,9,1X,1,16,1X,C'GROUP NOT FOUND',38X))

This runs but does not pickup the 2 records I expected to be labeled 'NO CUSTOMER FOR GROUP' in addition I had to comment out the lrecl of the output file of 80. So it creates a 400 byte file.
Was wondering if syncsort can do this?

Code'd

by **BillyBoyo** » Fri Apr 11, 2014 1:44 am

Are the conditions for file 3 also indicated by the 1/2 value of the match marker? If not, what do you want done with the remaining records?

by **Cobbhill** » Fri Apr 11, 2014 4:42 am

Yes, I had tried
OUTFIL FNAMES=SORTOF02,INCLUDE=(401,1,CH,NE,C'B'),
INCLUDE=(401,1,CH,EQ,C'2'),
BUILD=(C'000000000 ',102,16,1X,C'NO CUSTOMER FOR GROUP',32X)),
INCLUDE=(401,1,CH,EQ,C'1'),
BUILD=(38,9,1X,1,16,1X,C'GROUP NOT FOUND',38X)) but could not get that to work. Another thing I that is happening is I can flip the IFTHEN statements for SORTOF02 and every record will give me the message of the last IFTHEN. It is always missing the 2 records that go to file 3 in the 1st example of 3 files.

by **BillyBoyo** » Fri Apr 11, 2014 12:14 pm

Select all

OUTFIL FNAMES=SORTOF02,SAVE,
IFTHEN=(WHEN=(401,1,CH,EQ,C'2'),
BUILD=(C'000000000 ',102,16,1X,C'NO CUSTOMER FOR GROUP',32X)),
IFTHEN=(WHEN=NONE,
BUILD=(38,9,1X,1,16,1X,C'GROUP NOT FOUND',38X))

Try something like that. You need to check what SAVE is doing in the manual, and WHEN=NONE also.

You have to use IFTHEN for conditions. INCLUDE/OMIT= is just filtering records, you can have one per OUTFIL (one of either, not one of each) and the surplus one are just ignored in your case, so you had unconditional BUILD statements, so only ever saw results from the second.

by **Cobbhill** » Fri Apr 11, 2014 3:55 pm

Your solution worked perfect!!! I knew I could just run another step to combine the files from the 1st example of 3 output files but felt with the power of sort these days there had to be away. I will read up on save and when=none as you suggested.

Thanks alot,
Bob

IBM Mainframe Forum

Trying to combine 2 conditions to one file in a join.

Trying to combine 2 conditions to one file in a join.

Re: Trying to combine 2 conditions to one file in a join.

Re: Trying to combine 2 conditions to one file in a join.

Re: Trying to combine 2 conditions to one file in a join.

Re: Trying to combine 2 conditions to one file in a join.