Page 1 of 1

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

PostPosted: Thu Apr 10, 2014 7:40 pm
by Cobbhill
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.
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

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

PostPosted: Fri Apr 11, 2014 1:44 am
by BillyBoyo
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?

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

PostPosted: Fri Apr 11, 2014 4:42 am
by Cobbhill
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.

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

PostPosted: Fri Apr 11, 2014 12:14 pm
by BillyBoyo
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.

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

PostPosted: Fri Apr 11, 2014 3:55 pm
by Cobbhill
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