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> /
midrange.com
