IBM Mainframe Forum

[Daouchiche]

I have a request to built, from 2 files a new file using DFSORT or ICETOOL.
All files have record length 80 and FB format.

Thanks for your help.

Her are the files contains.

Input File1:
Select From tab1
Where pers = (
)
AND .....

Input File2:
124563
235412
125483

Ouput File3:
Select From tab1
Where pers = (
124563,
235412,
125483,
)
AND .....

[sergeyken]

Your Output File3 should give you a SYNTAX ERROR if you ever tried to execute it...

[sergeyken]

Step 0: do not forget to use the CODE tags when posting any code samples

Step 1: assign a "category number" to each group of lines in each dataset

Input File1:

Select all

0001 Select From tab1
0001 Where pers = (
0003                     )
0003 AND .....
 

Input File2:

Select all

0002 124563
0002 235412
0002 125483
 

Step2: MERGE (or reSORT) the concatenated dataset by this assigned group ID, with parameter EQUALS, and cut off the extra added field
Ouput File3:

Select all

Select From tab1
Where pers = (
                      124563,
                      235412,
                      125483,    -- <=== SYNTAX ERROR
                      )
AND .....

Both SORT steps can be combined into a single JCL step by using ICETOOL/SYNCTOOL

[Daouchiche]

Hi sergeyken,
Thanks for your replay. I did with SORT but I've to find away to add the coma instead of the last record.

//STEP1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DISP=SHR,DSN=Input.File1
// DD DISP=SHR,DSN=Input.File2
//SORTOUT DD SYSOUT=*
//SYSIN DD *
INREC IFTHEN=(WHEN=INIT,OVERLAY=(81:SEQNUM,8,ZD)),
IFTHEN=(WHEN=(1,1,CH,GE,C'0'),
OVERLAY=(81:C'00000002'))
SORT FIELDS=(81,8,CH,A),EQUALS
OUTREC BUILD=(1,80)
/*

The result is what am looking for but need the coma.

Select From tab1
Where pers = (
124563
235412
125483
)
AND .....

[sergeyken]

Will not respond unless you start using CODE tags

[sergeyken]

The way you have used is working correctly only for extremely limited statements to be combined. In those restricted situations is makes no sense to automate anything - just create a constant dataset.

Or, you can keep the first part with
"WHERE XXXXX IN ("
in one dataset, while the last part with
") ......"
- in a different one. Then just concatenate all three of them.

* * * * * * *

The most trivial method to avoid the "ending comma" problem is, to append a non-existing value at the end of the list.

BTW: equal sign doesn't work for list evaluation; operation IN is needed in that case.

Select all

WHERE XXXXX IN (
      111111,
      222222,
      ........,
      98765,
     -1          -- invalid value used after the last comma
)

This works OK without sophisticated checking for the last line of values.

[sergeyken]

The most simple and correct method of doing this should be as follows.

Select all

//GENSQL   EXEC PGM=SORT,        stop using idiotic names like STEP1 !!!                              
//            PARM='JP0"TABL",JP1"PERS"'        table and field names as parameters
//SYSOUT  DD SYSOUT=*                                
//SORTIN  DD DISP=SHR,DSN=Input.File2       - the only varying part of the SQL    
//SORTOUT DD SYSOUT=*                                
//SYSIN   DD *                                        
   INREC PARSE=(%1=(STARTAT=NONBLANK,
                    ENDBEFR=BLANKS,
                    FIXLEN=15)),
         BUILD=(10X,%1,C',')                  
   SORT FIELDS=COPY                    
   OUTFIL REMOVECC,
          HEADER1=(C'Select * From ',JP0,
                 /,C'Where ',JP1,C' IN ('),
          TRAILER1=(C'          -1     -- invalid ending value',
                  /,C'   )',
                  /,C'   . . . . whatever else . . . . .')                                
/*