SELECT operator

147 views Asked by At
//ICETSIM1 EXEC PGM=ICETOOL
//TOOLMSG  DD SYSOUT=*
//DFSMSG   DD SYSOUT=*
//SYMNOUT  DD SYSOUT=*
//NAMESIN   DD *

LINK_REC;1,45
 LINK_REFDATE;=,8,CH
 LINK;*,16,CH
 LINK_COLL;*,16,CH
 LINK_TYPE;*,3,CH
 LINK_LABEL;*,02,CH
    LINK_P_LABEL;=,1,CH
    LINK_S_LABEL;*,1,CH


//NAMESOUT DD DSN=&NAMES,DISP=(,PASS,DELETE),SPACE=(TRK,1)
//TOOLIN  DD *
COPY FROM(NAMESIN) TO(NAMESOUT)

//S01      EXEC PGM=ICETOOL
//TOOLMSG  DD SYSOUT=*
//DFSMSG   DD SYSOUT=*
//SYMNAMES DD DSN=&NAMES,DISP=(OLD,PASS)
//SYMNOUT  DD SYSOUT=*
//IN2      DD DISP=SHR,DSN=LINKS.001

SELECT FROM(IN2) TO(OU2) ON(LINK) FIRST USING(CTL2)

//CTL2CNTL DD *
OUTFIL FNAMES=OU2,
OUTREC=(LINK_REFDATE,16X,LINK,500X,LINK_TYPE,C'22')

This ICETOOL selects the first record for each LINK value in LINKS.001. The question is: does the SELECT operator expects the input to be sorted? If yes, it has to be sorted on all the fields of LINKS.001 (REF_DATE, LINK, LINK_COLL...)?

1

There are 1 answers

0
Bill Woodger On BEST ANSWER

I'm a big fan of SORT symbols/SYMNAMES, so nice to see you using that, and in a fairly advanced way. You only specify one start position for your record, and relate everything else to the previous field, which is the most flexible way to do it.

I didn't realise a semi-colon could be used, so I changed it to a comma. You can't have a blank line (you can have a comment, * in column one, and leave the rest of the line blank.

I don't know why you have the first step, so I've dropped it out until you explain. The best way to use the SYMNAMES DD is with a DSN= and then a PDS/PDSE with a member-name, then have your cards inside the member. For explanation, I use DD *.

In your USING file, you have SORT control cards, which is correct, but they must have a blank in column one.

OUTREC on OUTFIL is dated, available for backwards-compatibility, so I've changed it to BUILD (which is a synonym for OUTREC on OUTFIL, and FIELDS on INREC and OUTREC - see how much less confusing it is to stick to just BUILD?).

By default, ICETOOL's SELECT operator sorts the data on each ON field you specify, in the order specified. If your data is already in the correct sequence, you use a USING (which you already have anyway) and specify SORT FIELDS=COPY or OPTION COPY there. Your data will then nor be sorted again.

//S01      EXEC PGM=ICETOOL 
//TOOLMSG  DD SYSOUT=* 
//DFSMSG   DD SYSOUT=* 
//SYMNAMES DD * 
LINK_REC,1,45 
LINK_REFDATE,=,8,CH 
LINK,*,16,CH 
LINK_COLL,*,16,CH 
LINK_TYPE,*,3,CH 
LINK_LABEL,*,02,CH 
    LINK_P_LABEL,=,1,CH 
    LINK_S_LABEL,*,1,CH 
//SYMNOUT  DD SYSOUT=* 
//IN2      DD * 
1111111111111111111 
1111111111111111111 
2222222222222222222 
//OU2 DD SYSOUT=* 
//TOOLIN  DD * 

SELECT FROM(IN2) TO(OU2) ON(LINK) FIRST USING(CTL2)

//CTL2CNTL DD * 
 SORT FIELDS=COPY 
 OUTFIL FNAMES=OU2, 
        BUILD=(LINK_REFDATE, 
               16X, 
               LINK, 
               500X, 
               LINK_TYPE, 
               C'22')

The above, with my simple test-data, produces what you want. There were a couple of other typos (no OU2 DD, for instance) that I fixed to get it running.