How to convert carriage return to another char string in Informix SQL or 4GL

I am post this question for my co-worker, here is his questions. Any help would be appreciated. Hi, In a databse table there is a field of type character or string - or whatever informix calls it. This string contains new line characters or carriage returns. In a 4gl program this field is queried and printed. Just before this field is printed the line number internal variable "lineno" is , say, 10. Then this field is printed. Lets say the field has one new line character ( IN other words the field contains" Hello, How are you." ) As you can see the field has one new line character after "Hello," since there are two lines. Immediately after printing the above field value, the line number internal variable "lineno" is , 12. It should be 13 since two lines have been used by the field. This causes the "skip to top of page" command to not really skip to the top of the page and report format is all messed up. We need this fixed. But for now, as a work around, if I can replace any occurence of "new line" or "carriage return" with a space or a dash, I can live with that but unfortunately I can't find a way to type in the "new line" character in the function call. For example, in order to replace any occurence of "y" with a "z" in the string "xyz" I will use this sql function: replace("xyz", "y", "z") the result will be "xzz". Here I specified the character "y" to be replaced. How do I specify that I need to replace a "carriage return" or a "newline" character. I tried '^M' but this did not work.

Wayne T wrote: > I am post this question for my co-worker, here is his questions. Any > help would be appreciated. > > Hi, > > In a databse table there is a field of type character or string - or > whatever informix calls it. This string contains new line characters > or carriage returns. > In a 4gl program this field is queried and printed. > > Just before this field is printed the line number internal variable > "lineno" is , say, 10. Then this field is printed. Lets say the field > has one new line character > ( IN other words the field contains" > > Hello, > How are you." > ) > > As you can see the field has one new line character after "Hello," > since there are two lines. > > Immediately after printing the above field value, the line number > internal variable "lineno" is , 12. > It should be 13 since two lines have been used by the field. > > This causes the "skip to top of page" command to not really skip to > the top of the page and report format is all messed up. > We need this fixed. > > But for now, as a work around, if I can replace any occurence of "new > line" or "carriage return" with a space or a dash, I can live with > that > but unfortunately I can't find a way to type in the "new line" > character in the function call. > > For example, in order to replace any occurence of "y" with a "z" in > the string "xyz" > I will use this sql function: > > replace("xyz", "y", "z") > the result will be "xzz". > > Here I specified the character "y" to be replaced. > How do I specify that I need to replace a "carriage return" or a > "newline" character. > I tried '^M' but this did not work. '^M' is just a 2-character string. You probably wanted ASCII(13). The line number computation isn't immediately obvious. You print lines 10 and 11 to contain the data with a single new line, and expect the line number to jump to 13 instead of 12? Did someone write the question once with 2 newlines in the data, and then only partially amend the question? Or what? The data sample appears to have a blank line and then 'Hello,' and then 'How are you.'. If the data really has a single new line in it, then it seems to me that ISQL/ACE and I4GL are behaving magnificently. If the data really has several new lines in it, then there is room to think about 'bug'. Oh, and you didn't mention version information. Make a New Year's resolution - "I will always include version and platform information in questions posted to comp.databases.informix". Thanks! -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

Jonathan Leffler <jleffler@earthlink.net> writes: > line and then 'Hello,' and then 'How are you.'. If the data really > has a single new line in it, then it seems to me that ISQL/ACE and > I4GL are behaving magnificently. If the data really has several new > lines in it, then there is room to think about 'bug'. Not too surprising. I've had to pull some heinous tricks in my time to get Informix report writers to do things that their developers obviously never considered. One "line" is probably exactly defined as the output of one "print" statement. You'll probably have to write some code to extract each line from the variable and print them individually to get what you want. -- Forte International, P.O. Box 1412, Ridgecrest, CA 93556-1412 Ronald Cole <ronald@forte-intl.com> Phone: (760) 499-9142 President, CEO Fax: (760) 499-9152 My GPG fingerprint: C3AF 4BE9 BEA6 F1C2 B084 4A88 8851 E6C8 69E3 B00B

On Fri, 02 Jan 2004 09:55:31 -0500, Wayne T wrote: One idea that may solve the problem depending... The 4GL report writer normally pads pages to their defined length with newline characters. So having an extra newline printed by your embedded newline string which is not counted messes up the line count and misalligns the following pages. A solution is to tell the 4GL report module to use FORMFEED (^L) to paginate instead. This is done by placing the following line at the top of the REPORT module function in the OUTPUT section: OUTPUT TOP OF PAGE "^L" You may still have a problem with the report writer missing that a page has filled and so outputting a page with a single line on it. You can fix that also by keeping your own line count, adjusted for the embedded newlines, and forcing a page break (with SKIP TO TOP OF PAGE) when your own count indicates that a page boundary has been reached, then clear the counter in the PAGE HEADER section (so it also clears when the report writer pages for a subsection boundary that specifies a new page). Been doing this for years in combination with setting the 4GL page length to 99999 in order to get variable length paging. Art S. Kagel > I am post this question for my co-worker, here is his questions. Any help > would be appreciated. > > Hi, > > In a databse table there is a field of type character or string - or > whatever informix calls it. This string contains new line characters or > carriage returns. > In a 4gl program this field is queried and printed. > > Just before this field is printed the line number internal variable "lineno" > is , say, 10. Then this field is printed. Lets say the field has one new > line character > ( IN other words the field contains" > > Hello, > How are you." > ) > > As you can see the field has one new line character after "Hello," since > there are two lines. > > Immediately after printing the above field value, the line number internal > variable "lineno" is , 12. > It should be 13 since two lines have been used by the field. > > This causes the "skip to top of page" command to not really skip to the top > of the page and report format is all messed up. We need this fixed. > > But for now, as a work around, if I can replace any occurence of "new line" > or "carriage return" with a space or a dash, I can live with that but > unfortunately I can't find a way to type in the "new line" character in the > function call. > > For example, in order to replace any occurence of "y" with a "z" in the > string "xyz" > I will use this sql function: > > replace("xyz", "y", "z") > the result will be "xzz". > > Here I specified the character "y" to be replaced. How do I specify that I > need to replace a "carriage return" or a "newline" character. I tried '^M' > but this did not work.

Your function could be used as follows in any 4GL version: replace("xyz", ASCII(10), "-") It's a little harder in SPL, but in version 9 you can use EXECUTE PROCEDURE IFX_ALLOW_NEWLINE('T') to allow linefeeds in quoted strings. See Art's answer if you would prefer to fix using form feeds. Regards, Doug Lawry www.douglawry.webhop.org "Wayne T" <ntrieu@yahoo.com> wrote in message news:e6623a40.0401020655.4e1e1b73@posting.google.com... > I am post this question for my co-worker, here is his questions. Any > help would be appreciated. > > Hi, > > In a databse table there is a field of type character or string - or > whatever informix calls it. This string contains new line characters > or carriage returns. > In a 4gl program this field is queried and printed. > > Just before this field is printed the line number internal variable > "lineno" is , say, 10. Then this field is printed. Lets say the field > has one new line character > ( IN other words the field contains" > > Hello, > How are you." > ) > > As you can see the field has one new line character after "Hello," > since there are two lines. > > Immediately after printing the above field value, the line number > internal variable "lineno" is , 12. > It should be 13 since two lines have been used by the field. > > This causes the "skip to top of page" command to not really skip to > the top of the page and report format is all messed up. > We need this fixed. > > But for now, as a work around, if I can replace any occurence of "new > line" or "carriage return" with a space or a dash, I can live with > that > but unfortunately I can't find a way to type in the "new line" > character in the function call. > > For example, in order to replace any occurence of "y" with a "z" in > the string "xyz" > I will use this sql function: > > replace("xyz", "y", "z") > the result will be "xzz". > > Here I specified the character "y" to be replaced. > How do I specify that I need to replace a "carriage return" or a > "newline" character. > I tried '^M' but this did not work.