Seems odd, given the many years lacking Edit Word (EDTWRD) or Edit
Code (EDTCDE) [%editw() and %editc() as known elsewhere] and sporadic
topics as expressions of pining for that capability from the SQL, that
nobody seems to have made a post on any of the midrange fora about the
VARCHAR_FORMAT [aka TO_CHAR] getting the support for *editing* numeric
values; i.e. support beyond just the editing of TIMESTAMP values with a
format-string specification. There was at least one topic I found that
mentioned a new function, with the TO_CHAR scalar also mentioned in the
same message, but the new function of note was only since v7r2 and was
that of the ability to directly cast a DATE to a numeric with the digits
representing YYYYMMDD; e.g. using the DECIMAL, INTEGER, or ZONED casting
scalars, or the CAST expression.
Strange that I missed that addition about numeric value; I do not
recall seeing any mention on IBM DeveloperWorks (DW) or in any What's
New; but I noticed the feature is updated as far back in the Knowledge
Center as IBM i 7.1 rather than just IBM i 7.2 docs that I've started
referencing [though now that the system I am using is IBM i 7.3, I
really should move on a bit further forward]. Searching on `What's New
Scalar` in the 7.1 KC found a reference that the scalar function was
updated, but no mention of how; but looking at all of the topics under
the general topic page of What's New led to no indications of updates to
any scalars; huh.
Anyhow, I posted, because perhaps others may be unaware as well, and
they might want to try out the capabilities; the doc reference is v7r3,
but seems the same since v7r1:
[IBM i 7.3-> Database-> Reference-> SQL reference-> Built-in functions->
Scalar functions-> VARCHAR_FORMAT]
(
http://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/db2/rbafzscavarcharformat.htm)
"…
Numeric to Varchar
numeric-expression
An expression that returns a value of any built-in numeric data
type. If the argument is not a decimal floating-point value, it is
converted to DECFLOAT(34) for processing.
format-string
An expression that returns a built-in character string, graphic
string, or numeric data type. If the value is not a CHAR or VARCHAR data
type, it is implicitly cast to VARCHAR before evaluating the function.
format-string contains a template of how numeric-expression is to be
formatted. A format-string must contain a valid combination of the
listed format elements according to the following rules:
…"
Oddly, they also added support for a character-expression as the
first argument, yet there is no character-editing :-( per lack of the
ability to specify a second argument for that type. So for the lack of
a format-string to specify, just what is there about the capability to
/format/ the character string value, over, for example, just using the
VARCHAR casting scalar? The least it could have done, to be something
other than a duplicate of VARCHAR, is do the sensible thing and RTRIM
the value so that no strip/trim scalar has to be explicitly coded. I
see there is a form of /character editing/, for hex digits, with the
VARCHAR_FORMAT_BINARY scalar.
midrange.com
