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



Support for NetApp SyncSort for z/OS, Visual SyncSort, SYNCINIT, SYNCLIST and SYNCTOOL

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

Postby 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.
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
Cobbhill
 
Posts: 3
Joined: Wed Apr 09, 2014 8:22 pm
Has thanked: 0 time
Been thanked: 0 time

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

Postby 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?
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

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

Postby 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.
Cobbhill
 
Posts: 3
Joined: Wed Apr 09, 2014 8:22 pm
Has thanked: 0 time
Been thanked: 0 time

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

Postby BillyBoyo » Fri Apr 11, 2014 12:14 pm

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.
BillyBoyo
Global moderator
 
Posts: 3804
Joined: Tue Jan 25, 2011 12:02 am
Has thanked: 22 times
Been thanked: 265 times

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

Postby 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
Cobbhill
 
Posts: 3
Joined: Wed Apr 09, 2014 8:22 pm
Has thanked: 0 time
Been thanked: 0 time


Return to Syncsort/Synctool

 


  • Related topics
    Replies
    Views
    Last post