RE: building new fields using substr in sql

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

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.