Parameterized RUNSQLSTM with Two or More Substitutions

September 4, 2002 Timothy Prickett Morgan

Hey, Ted:

In the June 21 issue of Midrange Guru, you shared a Qshell sed command to create a source member that can be used under the Run SQL Statement (RUNSQLSTM) command.

Your example replaced one token. However, I need to make multiple changes using one sed command. For example, this is the pattern source member:

 Insert into MyFile                
     ( Select distinct a.LOGNBR       
       from File1 a, File2 b    
       Where a.ORGLOGNBR = 'LLLLLLL'  
         and b.LOGNBR    = 'LLLLLLL'  
         and b.SEQNBR    =  NNNN      
         and a.CNTL = b.CNTL )        

I want to change LLLLLLL and NNNN together with one sed command. Otherwise I have to create two new members.

Is it possible to change both at the same time with one sed command?

— Shafiq

Use the -e option, Shafiq. The -e option indicates that a sed command follows in the next parameter. You can repeat the -e option.

The following sed command contains two sed substitution commands, each one of which is preceded by the -e option.

sed -e 's/LLLLLLL/1234567/g' -e 's/NNNN/9876/g'  
/qsys.lib/mylib.lib/mysrcfile.file/mymbr.mbr  

If I run this sed command against the source code you provided me, this is the output I get.

 Insert into MyFile                  
     ( Select distinct a.LOGNBR      
       from File1 a, File2 b         
       Where a.ORGLOGNBR = '1234567' 
         and b.LOGNBR    = '1234567' 
         and b.SEQNBR    =  9876     
         and a.CNTL = b.CNTL )      

If I remember correctly, this option didn’t work for me when I tried it under V4R5. It does work correctly under V5R1.

This is a good illustration of why I started learning to use Qshell. I was intrigued by the Qshell utilities and wondered if I might be able to use them with source physical files.

— Ted

Sponsored By COMMON

REGISTER FOR COMMON IN DENVER, OCT. 13-17 Get the IT training you need by attending COMMON Users Group’s Fall 2002 IT Education Conference & Expo, October 13-17 in Denver. Early Bird registration is $1,150 until September 4. Choose from over 720 sessions and labs covering a wide range of industry topics. Also receive training from J.D. Edwards, MAPICS, and other vendors. Don’t miss out! Go to www.common.org