sql - RPG IV Files processing issue -

i still learning rpg , as/400. coming ms c#.net. moving iseries machine , trying pickup ibm rpg , cl programming more needed company.

the iseries version if reading right 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 issue.

i have flat file generated on old as400 machine , copied new iseries file delimited data (using ";" ) need reformat , export ftp further processing outside company. each record have either 29 or 28 columns. first thing using sqlrpgle program add delimiter records missing one. in 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+ :maxcount, :@delimcount   c+ qgpl.fincomming    c/end-exec            **/*  using delimiter fix data inserting delimiter in proper place */   c/exec sql    c+ update qgpl.fincomming  c+  set f00001 = insert(f00001,225,0,';')   c+ ( :maxcount  c+         - (  length(trim(f00001))    c+            - length(replace(trim(f00001),';','')) )  c+       ) > 0   c/end-exec    

then created 2 tables (pf) on iseries

table1, sql table 29 columns based on incoming file format, (all columns named , set length of data, columns text (a) type )

and second table (table2) have same layout table (table1) each columns of specific data type needed. i.e. id integer , dateof date etc.

no data not clean. date field might have spaces in or money field might have text in it.

i need best way move data table2 cleaning , validating during transfer.
sql way best statement big can not validate properly.

can 1 suggest (with example if possible) other ways or better way write sql it
have lot of errors: , if try run in strsql not code fits in screen.

select  trim(orderid) orderid ...,linenum) <> ''       trim(linenum)        else 9999 end linenum , trim(custnum) custnum , trim(ponum) ponum , case when trim(replace(orddate,0,'')) <> ''        cast(insert(insert(orddate,5,0,'/'),3,0,'/') date)  ... table1 

as requested: original flat file data [preceded first column headings c1-c29 records 29 effective columns of delimited data, second column headings c1-c28 *err-> pointing out expected delimiter , 30 blanks missing secondary records because each record should share same layout, , third embedded ruler-line show 225th position semicolon added prior sql code and optionally 30 blanks added effect maintaining 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 1 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 desired outcome generate ms sql process [preceded 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",,, 

i read fincomming directly rpg program, , not worry missing delimiter. guessing @ end of record anyway (you didn't tell that). parse out fileds in c/c++, standard c library available rpgiv. each record validate fields, , if valid, write directly table 2 mentioned in question. records errors written table 1 remediation.

now mention data needs ftp'd outside company, issue altogether. scott klement has api can use ftp files directly within rpg. web site http://www.scottklement.com.

and 1 more thing, given new rpg, might want learn free format rather fixed format variant modern way code rpg. sql statement in free format like:

   exec sql       update qgpl.fincomming       set f00001 = insert(f00001,225,0,';')       :maxcount - length(trim(f00001) - length(replace(trim(f00001),';',''))) > 0; 

there many ways generate comma seperated file database file. easiest use cl command cpytoimpf, prompt it, , can see options. command can drop file directly ifs translation ascii transfer ms-sql.

---

edit march 3, 2017

wow, looks folks using old stuff. if on release of i5/os, or os/400 prior v5r4, code looks this

 c/exec sql   c+  update qgpl.fincomming c+    set f00001 = insert(f00001,225,0,';') c+    :maxcount - length(trim(f00001) - length(replace(trim(f00001),';',''))) > 0; c/end-exec 

this fixed format, notice c spec. if coding in free format on old release, , need sql precompiler understand doing, need add /end-free , /free compiler directives around fixed form portions of code. @dam included link in comments.