Re: VARCHAR_FORMAT result length

Hi Birgitta,

What release are you on?  On the v7r3 box I have access to, this

Values(VarChar_Format(12345, '9999999990'), '|');


results in

                                          Data width . . . . . . :     262
                                          Shift to column  . . . . . .   1

....+....1....+....2....+....3    --- 23....+...24....+...25....+...26..
VALUES VALUES
12345                                                           |

Yes, the first value has an /actual/ length of 11, but the varchar returned has a length of 254, as noted in the manual.

If i remove the format like this

Values(VarChar_Format(12345), '|'); I get this:

Data width . . . . . . :      19
Position to line  . . . . .                        Shift to column  . . . . . .
....+....1....+.... VALUES       VALUES
12345          |

which does /not/ match the manual, which says that the varchar length is 42.

Either way, why doesn't the varchar length match the actual length?

--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx <mailto:petercdow@xxxxxxxxx>
pdow@xxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxx> /

On 7/31/2020 9:45 AM, Birgitta Hauser wrote:

If you just specify a number of digits without any decimal separator it is interpreted as Integer. An Integer consist of 10 digits. Your Format consists only of 5 characters. In this way the first 5 Digits of the Integer Value (0000012345) are taken an nothing returned.
Just either change the format to 10 digits or explicitly cast your number to DEC(5, 0)

Values(VarChar_Format(12345, '9999999990'), '|');

Values(VarChar_Format(Cast(12345 as Dec(5, 0)), '99990'), '|');

Mit freundlichen Grüßen / Best regards

Birgitta Hauser


"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
„Train people well enough so they can leave, treat them well enough so they don't want to.“ (Richard Branson)


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Peter Dow
Sent: Freitag, 31. Juli 2020 01:11
To: MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: VARCHAR_FORMAT result length

Hi Everyone,

I was trying out varchar_format() and ran across this:

values ( varchar_format(12345,'99990'), '|' )

....+....1....+....2....+....3....+....4....+....5..
VALUES
12345

Data width . . . . . . : 262
Shift to column . . . . . .
+...23....+...24....+...25....+...26..
VALUES
|


So I read the documentation for v7r3 about varchar_format
(https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_73/db2/rbafzscavarcharformat.htm)
and it says:

"The result is a string representation of/rounded-input-value/. The data type of the result is varying-length character or varying-length graphic based on the data type of theformat-string. If a single argument is specified the length attribute is 42. Otherwise the length attribute is 254. The actual length of the result is determined by/format-string/, if specified. Otherwise, the actual length of the result is the smallest number of characters that can represent the value of/rounded-input-value/. If the resulting string exceeds the length attribute of the result, the result will be truncated."


So I tried it without the format string:

values (12345, varchar_format(12345), '|' )

....+....1....+....2....+....3....+
VALUES VALUES VALUES
12,345 12345 |

The first test looks right according to the docs "Otherwise the length attribute is 254." When written to a file:

Field File Type Length
SEL0001 TESTVCF VARCHAR 254
SEL0002 TESTVCF VARCHAR 1


But the second does not: "If a single argument is specified the length attribute is 42." When written to a file:

Field File Type Length
SEL0001 TESTVCF VARCHAR 11
SEL0002 TESTVCF VARCHAR 1



The docs also say:

"The actual length of the result is determined by/format-string/, if specified. Otherwise, the actual length of the result is the smallest number of characters that can represent the value of/rounded-input-value/."

Besides the obvious that the docs are not matching the actual results, why is the length attribute not set to the actual length? Expecting a 5-character result and getting 254 requires another function to make it a reasonable length for a report.

--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx <mailto:petercdow@xxxxxxxxx>
pdow@xxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxx> /