I am still learning RPG and AS/400. coming from MS C#.NET.
we are currently moving to iSeries machine and I am trying to pickup the IBM RPG and CL programming as it is more needed for the company.

the iSeries version if I am reading it right is
dspdtaara QSS1MRI: ==> "V7R1M000 2924"

DSPSFWRSC+ F11 : ==>

```
 5770999   *BASE    5050     *CODE   QSYS        V7R1M0 L00 
 5770SS1   *BASE    5050     *CODE   QSYS        V7R1M0 L00 
 5770SS1   *BASE    2924     *LNG    QSYS        V7R1M0 L00    
 ... 
 5770WDS   56       5101     *CODE   QDEVTOOLS   V7R1M0  
 5770WDS   60       5050     *CODE   QDEVTOOLS   V7R1M0  

```

here is my issue.

I have a flat file that is generated on the old AS400 machine and is copied to the new iSeries
file is a delimited data (using ";" ) that I need to reformat and export to FTP for further processing by outside company. each record have either 29 or 28 columns. thus first thing I do is using an SQLRPGLE program add an extra delimiter to the records that are missing one. in the flat file itself

```
 C/EXEC SQL 
 C+ SELECT  
 C+   MAX((length(trim(F00001))- length(REPLACE(trim(F00001),';',''))))
 C+ , MIN((length(trim(F00001))- length(REPLACE(trim(F00001),';',''))))
 C+ INTO :MaxCount, :@DelimCount 
 C+ FROM QGPL.fIncomming  
 C/END-EXEC        

  **/*  using the delimiter could fix data by inserting a delimiter in the proper place */

 C/EXEC SQL  
 C+ UPDATE QGPL.fIncomming
 C+  SET F00001 = INSERT(F00001,225,0,';') 
 C+ WHERE ( :MaxCount
 C+         - (  length(trim(F00001))  
 C+            - length(REPLACE(trim(F00001),';','')) )
 C+       ) > 0 
 C/END-EXEC   

```

then I created 2 tables (PF) on iSeries 

Table1, which is an SQL table with 29 columns based on the incoming file format, (All columns are named and set to length of the data, but all columns are text (A) type )

and the second table (Table2) which have exactly the same layout as table (Table1) but each columns is of specific data type as needed. 
i.e. id is INTEGER and dateof is DATE etc.

no the data is not clean. thus a date field might have spaces in it or money field might have text in it.

I need the best way to move the data to table2 cleaning up and validating it during the transfer.  

the SQL way would be the best but the statement is so big I can not truly validate it properly.

can some one suggest (with an example if possible) other ways or better way to write SQL for it  

I basically have this but get a lot of errors: and if I try to run it in STRSQL not all the code fits in a screen.

```
SELECT
 TRIM(ORDERID) as ORDERID
...,LINENUM) <> ''
      THEN TRIM(LINENUM) 
      ELSE 9999 END as LINENUM
, TRIM(CUSTNUM) as CUSTNUM
, TRIM(PONUM) as PONUM
, CASE WHEN TRIM(REPLACE(ORDDATE,0,'')) <> ''
       THEN CAST(INSERT(INSERT(ORDDATE,5,0,'/'),3,0,'/') as Date) 
...
from Table1

```

AS REQUESTED:
This is the original flat file data [preceded first by column headings c1-c29 for records with 29 effective columns of delimited data, then second by column headings c1-c28 with a `**\*Err->**` pointing out where the expected delimiter and 30 blanks are missing for the secondary records *because each record should share the **same** layout*, and third by an embedded ruler-line to show the 225th position where the semicolon would be added with the prior SQL code *and optionally 30 blanks could be added to effect maintaining the fixed-length layout*.]:

```
---c1----c2---c3----c4---------c5-------c6-------c7-------c8----------c9-----------c10-------c11--------------c12--------------c13----c14-c15---c16---c17---c18------c19---------c20------c21-----c22-------------c23-----------------------c24------------------c25----c26----c27------c28-----c29---
---c1----c2---c3----c4---------c5-------c6-------c7-------c8----------c9-----------c10-------c11--------------c12--------------c13----c14-c15---c16---c17---c18------c19---------c20------c21-----c22-------------c23-----*ERR->---c24--c25----c26-------c27-----c28-- **missing one column!**
....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9....+...10....+...11....+...12....+...13....+...14....+...15....+...16....+...17....+...18....+...19....+...20....+...21....+...22....+...23....+...24....+...25....+...26....+...27....+...28....+...29....+
1596555;001;10010;TEST5     ;01062015; 1213.00; 1219.00;  17.000;NET 30 DAYS ;            ;543534241;TOYYO1/5                ;  14OZ  ;T; 5.00; .500;   1; 560.00;          ;   560.00;01292015;5379602;** 2ND DAY **           ;5XDFSDFFGFGHGHGH16            ;      ;   ;          ;        ; 
1596555;   ;10010;          ;        ;        ;        ;        ;            ;            ;         ;                        ;        ; ;     ; .   ;    ;       ;          ;         ;01292015;5379602;                        ; 16.60;FRT;          ;        ; 
1598556;001;10021;TEST      ;02112015; 1237.00; 1207.00;  17.000;NET 30 DAYS ;            ;567860502;45GGH/4019              ;  10OZ  ;R;12.50; .000;   1; 105.42;          ;   105.42;02122015;5380313;** 2ND DAY **           ;3HGFH5456GFHFG5G27            ;      ;   ;          ;        ;
1598556;   ;10021;          ;        ;        ;        ;        ;            ;            ;         ;                        ;        ; ;     ; .   ;    ;       ;          ;         ;02122015;5380313;                        ; 13.19;FRT;          ;        ; 
1598557;001;10067;020415    ;02042015; 1283.00; 1238.00;  18.000;NET 30 DAYS ;            ;657870142;FTKG061/11              ;  14OZ  ;R;     ; .330;   1; 358.00;          ;   358.00;02092015;5380071;** 2ND DAY **           ;3NHJYJ64646GHJGHJ8            ;      ;   ;          ;        ;
1598557;   ;10067;          ;        ;        ;        ;        ;            ;            ;         ;                        ;        ; ;     ; .   ;    ;       ;          ;         ;02092015;5380071;                        ; 15.09;FRT;          ;        ;

```

And this is the desired outcome that is currently generate by MS SQL process [preceded by an embedded ruler-line]:

```
....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9....+...10....+...11....+...12....+...13....+...14....+...15....+...16....+...17....+...18....+...19....+...20....+...21....+
"1596555",1,"10010","TEST5",01/06/2015, 1213.00, 1219.00, 17.00,"NET 30 DAYS",,"543534241","TOYYO1/5","14OZ","T",5.00,0.50,1, 560.00,, 560.00,01/29/2015,"5379602","** 2ND DAY **","5XDFSDFFGFGHGHGH16",,,,,
"1596555",9999,"10010",,,,,,,,,,,,,,,,,,01/29/2015,"5379602",,,"16.60","FRT",,,
"1598556",1,"10021","TEST",02/11/2015, 1237.00, 1207.00, 17.00,"NET 30 DAYS",,"567860502","45GGH/4019","10OZ","R",12.50,0.00,1, 105.42,, 105.42,02/12/2015,"5380313","** 2ND DAY **","3HGFH5456GFHFG5G27",,,,,
"1598556",9999,"10021",,,,,,,,,,,,,,,,,,02/12/2015,"5380313",,,"13.19","FRT",,,
"1598557",1,"10067","020415",02/04/2015, 1283.00, 1238.00, 18.00,"NET 30 DAYS",,"657870142","FTKG061/11","14OZ","R",,0.33,1, 358.00,, 358.00,02/09/2015,"5380071","** 2ND DAY **","3NHJYJ64646GHJGHJ8",,,,,
"1598557",9999,"10067",,,,,,,,,,,,,,,,,,02/09/2015,"5380071",,,"15.09","FRT",,,

```