1) As danglauser and Peter.Vanroose wrote,
DB2 for z/OS(and DB2 for iSeries) support cast to VARCHAR from DECIMAL.
I found same descriptions on "DB2 SQL Reference for z/OS" and "DB2 SQL Reference for iSeries".
Quote:
VARCHAR
.....
.....
Decimal to Varchar
.....
..... Leading zeros are not included. If the argument is negative, the result
begins with a minus sign. Otherwise, the result begins with a digit.
.....
|
But, DB2 for LUW(including version 9.5) doesn't suppot cast to VARCHAR from DECIMAL.
2) Nitya's expression(nitya_original) returns two decimal points for negative value.
And I think that a decimal function and two outer char functions are not neccesary.
I tried to fix the issue(nitya_modified). But, I couldn't do it(minus sign is not displayed for 0 > field_value > -1).
So, I tried other expressions(strip_a, strip_b and strip_c).
Code:
------------------------------ Commands Entered ------------------------------
connect to SAMPLE ;
------------------------------------------------------------------------------
Database Connection Information
Database server = DB2/NT 9.5.2
SQL authorization ID = DB2ADMIN
Local database alias = SAMPLE
A JDBC connection to the target has succeeded.
------------------------------ Commands Entered ------------------------------
SELECT d
, CHAR(RTRIM(char(integer(d))) || '.'
|| char(substr(char(decimal(d,17,2)),17))) nitya_original
, RTRIM(char(integer(d))) || '.' || substr(char(ABS(d)),17) nitya_modified
, CASE WHEN d < 0 THEN '-' ELSE '' END
|| CASE INT(d) WHEN 0 THEN '0' ELSE '' END
|| STRIP(CHAR(ABS(d)),L,'0') strip_a
, SUBSTR('-0',1+SIGN(INT(SIGN(d))+1),2-ABS(SIGN(INT(d)))-SIGN(INT(SIGN(d))+1))
|| STRIP(CHAR(ABS(d)),L,'0') strip_b
, RTRIM(SUBSTR('-0 ',2+SIGN(INT(d*2-1+6e-17)),2-ABS(SIGN(INT(d)))))
|| STRIP(CHAR(ABS(d)),L,'0') strip_c
FROM
( SELECT DEC(d,18,3) AS d
FROM
( VALUES 12.25, 100406.009, 1., 0.999, 0.001
, 0, -0.001, -0.999, -1., -100406.009) AS test_data(d)
)
;
------------------------------------------------------------------------------
D NITYA_ORIGINAL NITYA_MODIFIED STRIP_A STRIP_B STRIP_C
-------------------- --------------- ---------------- ---------------------- ---------------------- -----------------------
12.250 12.25 12.250 12.250 12.250 12.250
100406.009 100406.00 100406.009 100406.009 100406.009 100406.009
1.000 1.00 1.000 1.000 1.000 1.000
0.999 0.99 0.999 0.999 0.999 0.999
0.001 0.00 0.001 0.001 0.001 0.001
0.000 0.00 0.000 0.000 0.000 0.000
-0.001 0.00 0.001 -0.001 -0.001 -0.001
-0.999 0..99 0.999 -0.999 -0.999 -0.999
-1.000 -1..00 -1.000 -1.000 -1.000 -1.000
-100406.009 -100406..00 -100406.009 -100406.009 -100406.009 -100406.009
10 record(s) selected.