Re: strsql how to cram fields together

On 02-Apr-2014 09:30 -0700, Stone, Joel wrote:

OK. SUBSTR works as I would expect. Thanks

The confusion (for me) was that in Paul Conte's SQL/400 book [great
book], it shows Left(string,length) is identical to
substr(string,1,length).

Maybe that is accurate only if string is NOT var length???
<<SNIP>>

See my comment in the earlier quoted reply, about the effect of the LEFT() being "atypical" to the desirable effect. That was a change made [long ago], to be consistent with the other DB2 :-( Notice the contrast from one release to the next [no change flagging apparent], and then compare and contrast with the latest release; all doc snippets included below in this reply and the quoted replies.

The below doc links do not position properly; search within the page for "LEFT function" [minus the delimiters] to locate each of the specific references presented as snippets of quoted text below:

<http://publib.boulder.ibm.com/iseries/v5r1/ic2924/info/db2/rbafzmstscale.htm#HDRSCALEFT>
"... The LEFT function returns the leftmost specified number of characters from the string. º29º ...
...
_length_
...
If length is specified by an integer constant, the result is a fixed-length string. In all other cases, the result is a varying-length string with a length attribute that is the same as the length attribute of string. ...
...
º29º The LEFT scalar function returns the same results as: SUBSTR(string, 1, length).
..."

<http://publib.boulder.ibm.com/iseries/v5r2/ic2924/info/db2/rbafzmst02.htm#ToC_415>
"...
The LEFT function returns the leftmost integer bytes of string-expression.
...
... The result of the function is a varying-length string with a length attribute that is the same as the length attribute of string-expression ..."

Regards, Chuck

On 3/28/2014 4:22 PM, CRPence wrote:

On 28-Mar-2014 13:35 -0700, Stone, Joel wrote:

Why doesn't LEFT(fieldname,10) shorten the field for display?

The report writer is presenting the data as the result of the
expression, according to the data type\length. In this case,
effectively the same as the DDL for "fieldname".

The result of the LEFT scalar is atypical for what IMO, most
would desire. The result is effectively the same data type as
the _expression_ that is the first argument, but with the
*length* attribute of that _expression_, *not* the _length_
specified as the second argument; "effectively", because the type
is the variable-length variation on that data type. The length
merely requests how much of the data should be picked
[substring\substrung] from the expression, and full data typing
must be explicitly specified additionally. For example:
VARCHAR(RTRIM(LEFT(fieldname, 10)), 15)

<http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzscaleft.htm>
IBM i 7.1 Information Center -> Database -> Reference -> SQL
reference -> Built-in functions -> Scalar functions
_LEFT_
"The LEFT function returns the leftmost integer characters of expression.

>>-LEFT--(--expression--,--integer--)-------------------><

...

The result of the function is a varying-length string with a
length attribute that is the same as the length attribute of
expression and a data type that depends on the data type of
expression:
..."
<<SNIP>>