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",,,
I would read the FIncomming directly into the RPG program, and not worry about that missing delimiter. I am guessing it is at the end of the record anyway (you didn't really tell us that). Parse out the fileds as you would in C/C++, the standard C library is available to RPGIV. For each record validate the fields, and if all are valid, write directly to Table 2 mentioned in your question. Records with errors would be written to Table 1 for remediation.
Now you mention that the data needs to be FTP'd to an outside company, that is another issue altogether. Scott Klement has an API that you can use to FTP files directly from within RPG. His web site is http://www.scottklement.com.
And one more thing, given that you are new to RPG, you might want to learn free format rather than the fixed format variant as that is the modern way to code RPG. That SQL statement in free format would look like:
exec sql
UPDATE qgpl.fIncomming
SET F00001 = INSERT(F00001,225,0,';')
WHERE :MaxCount - length(trim(F00001) - length(REPLACE(trim(F00001),';',''))) > 0;
There are many ways to generate a comma seperated file from a database file. The easiest is to use CL command CPYTOIMPF, prompt it, and you can see the options. That command can also drop the file directly into the IFS with translation to ASCII for transfer to MS-SQL.
Edit March 3, 2017
Wow, looks like some folks are using some really old stuff. So if you are on a release of i5/OS, or OS/400 prior to V5R4, the code looks like this
c/exec sql c+ UPDATE qgpl.fIncomming c+ SET F00001 = INSERT(F00001,225,0,';') c+ WHERE :MaxCount - length(trim(F00001) - length(REPLACE(trim(F00001),';',''))) > 0; c/end-exec
This is fixed format, notice the c spec. If you are coding in free format on this old release, and need the SQL precompiler to understand what you are doing, you will need to add /end-free and /free compiler directives around the fixed form portions of your code. @Dam included a link in the comments.