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.