SteveFletcher Feb 14, 2009   11:11 PM GMT

Thanks for your response Phil. I'm hoping to do this using SQL. I've tried several things, but can't get it to work. This seems the most promising, but I'm not quite there:

WkLib = 'MYLIB';                                    
WkTable = 'OTR' + %Char(%SubDt(%Timestamp:*MS));        
WkOutFile = WkLib + '/' + WkTable;                      
WkFld = 'Create Table '+ WkOutFile + ' LIKE FULOTRWT';  
Exec Sql Execute Immediate :WkFld;  

Exec Sql PREPARE S1 FROM :SqlStmt;      
Exec Sql DECLARE C1 CURSOR FOR S1;      
Exec Sql OPEN C1;           

WkFld = 'Insert Into ' + WkOutFile + ' Values (@Fulotrwt)';

Exec Sql FETCH NEXT FROM C1 INTO :@Fulotrwt :@IndVar;       
                                                            
Dow SqlCod = 0;                                             

  Exec Sql Execute Immediate :WkFld;                        
                                                            
  Exec Sql FETCH NEXT FROM C1 INTO :@Fulotrwt :@IndVar;     
                                                            
Enddo;          

First, I create the table named OTR123000 (varying number) in library MYLIB like the existing table FULOTRWT. Then I prepare/declare/open a cursor for a passed-in sql statement to retrieve data from an existing table. Then I create a variable (WkFld) with the sql statement to be executed (@Fulotrwt is a data structure based on the existing table), fetch a record, and attempt to insert it into the dynamic table (repeatedly). Each time the

Exec Sql Execute Immediate :WkFld;

runs I get an sql 206 error with the message "Column @FULOTRWT not in specified tables." I also tried naming the columns specifically instead of the data structure, but then I get the same error but with the column name instead of the data structure. I would appreciate it if anyone can tell me what I'm missing here. Thanks, Steve

80 pointsBadges:

philpl1jb Feb 15, 2009   7:14 PM GMT

Steve I'm not using RPGSQL enough to be sure WkFld = 'Insert Into ' + WkOutFile + ' Values (@Fulotrwt)'; 1. should there be a : in :@FULOTRWT above? or expand it WkFld = 'Insert Into ' + WkOutFile + ' Values (' + Field1 + ',' + Field2+',''CharFld3+''')' something like this - note I've provided double quotes arount the in spots where SQL should be passed a single quote. Phil

54,090 pointsBadges:

SteveFletcher Feb 15, 2009   9:54 PM GMT

I found the answer over on SystemiNetwork with the help of Scott Klement. Use parameter markers, one for each field/column. Set the sql statement as: WkFld = 'Insert Into ' + WkOutFile + ' Values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)'; Prepare the statement: Exec Sql PREPARE S2 from :WkFld; Then, execute (instead of the Execute Immediate statement) in the loop: Exec Sql Execute S2 USING :@Fulotrwt :@IndVar; Thanks again for the assistance. Steve

80 pointsBadges: