Re: strsql how to cram fields together

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

When I code CHARACTER_LENGTH(LEFT(fieldname,10)) it returns the
shorter length of 10 instead of the orig field length of 100.

At first blush, I infer that is a defect. However, there is a documented ambiguity for results of padded varying-length string data, so that may cover the result; i.e. sometimes the blank pad are significant, and other times they are not, and the onus is on the user to ensure consistently trimmed results to avoid /unpredictable/ results.

<http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzscacharlenf.htm>
IBM i 7.1 Information Center -> Database -> Reference -> SQL reference -> Built-in functions -> Scalar functions
_CHARACTER_LENGTH_
"The CHARACTER_LENGTH or CHAR_LENGTH function returns the length of a string expression.

>>-+-CHARACTER_LENGTH-+--(--expression--)---------------><
'-CHAR_LENGTH------'

...

If expression is a character string or graphic string, the result is the number of characters in the argument (not the number of bytes). A single character is either an SBCS, DBCS, or multiple-byte character. If expression is a binary string, the result is the number of bytes in the argument. The length of strings includes trailing blanks or hexadecimal zeroes. The length of a varying-length string is the actual length, not the maximum length.
..."