× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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> /




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.