Insert records from File1 between records of File2



IBM's flagship sort product DFSORT for sorting, merging, copying, data manipulation and reporting. Includes ICETOOL and ICEGENER

Insert records from File1 between records of File2

Postby Daouchiche » Wed Nov 10, 2021 6:37 am

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 .....
Daouchiche
 
Posts: 17
Joined: Thu Jul 24, 2014 9:36 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Insert records from File1 between records of File2

Postby sergeyken » Wed Nov 10, 2021 8:54 pm

Your Output File3 should give you a SYNTAX ERROR if you ever tried to execute it...
Javas come and go, but JCL stays forever.
User avatar
sergeyken
 
Posts: 235
Joined: Wed Jul 24, 2019 10:12 pm
Has thanked: 5 times
Been thanked: 21 times

Re: Insert records from File1 between records of File2

Postby sergeyken » Wed Nov 10, 2021 9:01 pm

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:
0001 Select From tab1
0001 Where pers = (
0003                     )
0003 AND .....
 

Input File2:

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 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
Javas come and go, but JCL stays forever.
User avatar
sergeyken
 
Posts: 235
Joined: Wed Jul 24, 2019 10:12 pm
Has thanked: 5 times
Been thanked: 21 times

Re: Insert records from File1 between records of File2

Postby Daouchiche » Wed Nov 10, 2021 10:42 pm

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 .....
Daouchiche
 
Posts: 17
Joined: Thu Jul 24, 2014 9:36 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Insert records from File1 between records of File2

Postby sergeyken » Thu Nov 11, 2021 12:07 am

Will not respond unless you start using CODE tags
Javas come and go, but JCL stays forever.
User avatar
sergeyken
 
Posts: 235
Joined: Wed Jul 24, 2019 10:12 pm
Has thanked: 5 times
Been thanked: 21 times

Re: Insert records from File1 between records of File2

Postby sergeyken » Thu Nov 11, 2021 12:21 am

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.

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.
Javas come and go, but JCL stays forever.
User avatar
sergeyken
 
Posts: 235
Joined: Wed Jul 24, 2019 10:12 pm
Has thanked: 5 times
Been thanked: 21 times

Re: Insert records from File1 between records of File2

Postby sergeyken » Thu Nov 11, 2021 12:43 am

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


//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 . . . . .')                                
/*    
 
Javas come and go, but JCL stays forever.
User avatar
sergeyken
 
Posts: 235
Joined: Wed Jul 24, 2019 10:12 pm
Has thanked: 5 times
Been thanked: 21 times


Return to DFSORT/ICETOOL/ICEGENER

 


  • Related topics
    Replies
    Views
    Last post