Sounds to me like you're somehow creating VARCHAR fields by using SUBSTR in
SQL. Those two bytes are no control bytes, rather they are a short integer
value specifying how long the field is (how many chars it has).
If that's truly the case, remedy is simple, CAST your substring to a CHAR.
You can do it by using an explicit CAST keyword, i.e.:
CAST(SUBSTR(myBigfield, 10, 5) AS CHAR(5))
Or you can try implicit cast, i.e.:
CHAR(SUBSTR(myBigField, 10, 5))
All that said, I'm a bit confused by your distinction between "a whole field
that already existed" and field you "built".
Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
Subject: building new fields using substr in sql
I have been substring data from pieces of
fields and outputting the fields to a physical file
created new by the sql run
I keep getting 2 extra positions of "non-diplayable"
data in front of any field I "built", but not in front
of a whole field that already existed,
this causes the desired record lenth to be off
by multiples of 2 (for each occurrance of a substr'd field)
How do I get around those control bytes so they do
NOT add to my record length.
thanks in advance for your help.
This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact