RE: building new fields using substr in sql

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".

HTH, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
www.centerfieldtechnology.com


-----Original Message-----
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.