RE: Replacing carriage returns in a data file

My apologies for not responding to everyone who replied to my e-mail
This project is finally catching up to me and I slept for 10 hours straight - the first time in over a year
Thank you to everyone - but I discovered the reason why this started to happen
Trying not to bore everyone - we installed a new front end for order entry, that had to feed the as/400 to fulfill the orders.
Needless to say - some molding/cajoling/changes were required on both sides.
One of the changes that has to take place is how comments for the order were entered on the front end and then passed along to th4e AS/400
Users were told to enter so many characters per line of comment, then go to the next line.
In going to the next line, carriage returns were entered - but the front end didn't remove/replace them and people being people, backtracked to where they THOUGHT they ended
Needless to say - it wasn't
The front-end software is building something to rectify this situation

Again - thanks for everyone's response - I still managed to learn quite a few things

Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: Thursday, August 15, 2013 3:36 AM
To: 'Midrange Systems Technical Discussion'
Subject: AW: Replacing carriage returns in a data file

If embedded SQL would be an option for replacing characters in a stream file, you may try the following example:

D CLOBFileFrom S SQLType(CLOB_File)
D CLOBFileTo S SQLType(CLOB_File)
/Free
CLOBFileFrom_Name = '/home/Hauser/MyFile.txt';
CLOBFileFrom_NL = %Len(%Trim(CLOBFileFrom_Name));
CLOBFileFrom_FO = SQFRD; //Read Only

CLOBFileTo = CLOBFileFrom;
CLOBFileTo_FO = SQFOVR; //Replace if exists

Exec SQL Set :CLOBFileTo = Replace(:CLOBFileFrom, x'0D25', '');
If SQLCODE < *Zeros;
//Handle SQL Errors
EndIf;
Return;
/End-Free

BTW file reference variables (--> SQLType(CLOB_File), SQLType(DBCLOB_File),
SQLType(BLOB_FILE) and all the SQLType(XML_?LOB_FILE)s) can be managed with SQL functions like any character variable.
In this way the streamfile could easily be written directly into a database table directly with embedded SQL (without CPYFRMxxx command).

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) "What is worse than training your staff and losing them? Not training them and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Alan Shore
Gesendet: Wednesday, 14.8 2013 22:52
An: Midrange Systems Technical Discussion
Betreff: RE: Replacing carriage returns in a data file

This day just keeps getting better and better (sarcasm switch off) I have now realized that the file (with the carriage return) is created using the command CPYFRMSTMF And the carriage return is in the stream file This then causes the one record to be split into 2 (or more) in the table.
I have to replace the carriage return in the ifs prior to the CPYFRMSTMF command I cannot find any examples of using SQL on an ifs file.


Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640


-----Original Message-----
From: Alan Shore
Sent: Wednesday, August 14, 2013 4:20 PM
To: Midrange Systems Technical Discussion
Subject: RE: Replacing carriage returns in a data file

Thanks Luis
Let me have a test using what you supplied MUCH appreciated

Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Luis Rodriguez
Sent: Wednesday, August 14, 2013 4:13 PM
To: Midrange Systems Technical Discussion
Subject: Re: Replacing carriage returns in a data file

Alan,

You could try executing the following SQL statement:

update @test/alan_cr set alan_cr = replace(alan_cr, X'0D', '\')

IIRC, the hex for a CR is X'0D', so this would place a '\' in its place (or whatever character you need). You could call this inside a CL using the QSH
DB2 instruction.

HTH,
Luis Rodriguez
IBM Certified Systems Expert - eServer i5 iSeries