On 11-Oct-2016 08:57 -0500, Dan wrote:
Just tried it on our v7r1 box.
I noticed the IBM i 7.1 that I use does not have the capability yet;
when the docs were updated, they did not also show in the change flags
what is the minimum TR or PTF level. I get sqlcode=-171 msg SQL0171
"Argument 1 of function TO_CHAR not valid."
select rtrim(varchar_format( SU_CRGID, '999999999999999' )) as Chrg
, rtrim(varchar_format( SU_CUSID, '999999999999999' )) as CusI
, rtrim(varchar_format( SU_ACCT , '999999999999999' )) as Acct
, rtrim(varchar_format( SU_OWNID, '999999999999999' )) as OwnI
from charges
Each of the four fields is defined as 15,0 zoned decimal. Including
the implied leading sign, each of these should only take up 16
characters. However, there's 240 blanks between each of these fields.
Field 1 begins in column 1, field 2 begins in column 257, field 3
begins in column 513, and field 4 begins in column 769. The report is
1022 characters wide.
The report writer defines a fixed/static layout, built for the
largest possible value; combined with the SQL defining an exceedingly
large VARCHAR size to accommodate the result for that constant-szied
fifteen character mask makes for an awful effect. No idea if that
ginormous length is a defect, or just /crappy/ standards or /crappy/ design.
The RTRIM is superfluous; the VARCHAR of VARCHAR_FORMAT has done that
work already. Use the CHAR casting scalar for SUBSTR scalar with a
constant length in place of the RTRIM to get the desired edited result:
with charges ( SU_CRGID, SU_CUSID, SU_ACCT , SU_OWNID ) as
( values ( dec( -54321 , 16 )
, dec( 10987654321 , 16 )
, dec( 543210987654321 , 16 )
, dec( 0 , 16 )
)
)
select char(to_char( SU_CRGID, '999999999999999' ), 16) as Chrg
, char(to_char( SU_CUSID, '999999999999999' ), 16) as CusI
, char(to_char( SU_ACCT , '999999999999999' ), 16) as Acct
, char(to_char( SU_OWNID, '999999999999999' ), 16) as OwnI
from charges
-- likeness of report for the above query:
....+....1....+....2....+....3....+....4....+....5....+.... …..7
CHRG CUSI ACCT OWNI …
-54321 10987654321 543210987654321 … 0
******** End of data ********
with charges ( SU_CRGID, SU_CUSID, SU_ACCT , SU_OWNID ) as
( values ( dec( -54321 , 16 )
, dec( 10987654321 , 16 )
, dec( 543210987654321 , 16 )
, dec( 0 , 16 )
)
)
select char(to_char( SU_CRGID, '999999999999999MI' ), 15) as Chrg
, char(to_char( SU_CUSID, '999999999999999MI' ), 15) as CusI
, char(to_char( SU_ACCT , '999999999999999MI' ), 15) as Acct
, char(to_char( SU_OWNID, '999999999999999MI' ), 15) as OwnI
from charges
-- likeness of report for the above query:
....+....1....+....2....+....3....+....4....+....5....+....6....+.
CHRG CUSI ACCT OWNI
54321 10987654321 543210987654321 0
******** End of data ********
Frankly, I would have been happy to have a setting in STRSQL's
Change Session Attributes to set the thousands' separator to *NONE.
We can set the date and time format and separator, and the decimal
point character, but somehow no one figured we might also want
suppress the thousands' separator.
The Query/400 report writer has the capability, and we easily could
have offered a different default editing to the Start Interactive SQL
Session (STRSQL). I do not recall anyone ever asking; they did ask for
and got the ability to set the screen size preference and pass their own
choice of decimal vs thousand/group separator. Perhaps somebody wanted
to keep consistency between the two report writers used for numeric
editing; though the other one [from QM, for remote connections] was
already different, minimally for having added a line of dashes between
the column headings and data, but I recall the other report writer also
took even another line from the number of rows per screen resulting in
more wasted /real estate/ on the screen.
midrange.com
