| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
|
09-09-09, 11:00
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Formatting the decimal string
|
|
The following query give you the easiest way to format the decimal string:
Code:
with input (number) as
(select 1123167890.4327 from sysibm.sysdummy1
)
,
transform_1 (num, part, rem, trans, fract, k ) as
(select number, int(0), int(number), varchar('', 150), number - int(number), 12
from input
union all
select
number, int(rem / power(10, k - 3)) , mod(rem, power(10, k - 3)),
strip(case when int(rem / power(10, k - 3)) = 0 and trans <= ' ' then ''
when int(rem / power(10, k - 3)) > 0 and trans <= ' ' then
substr(digits(int(rem / power(10, k - 3))), 8, 3)
else trans || ',' || substr(digits(int(rem / power(10, k - 3))), 8, 3)
end
|| case when k - 3 = 0 and fract > 0 then strip(varchar(fract), l, '0') else '' end, l, '0')
, fract, k - 3
from transform_1, input
where k - 3 >= 0
)
select num "Input.................", trans "Result"from transform_1 tr where k = 0
So we'll get result:
Quote:
Input................. Result
1123167890.4327 1,123,167,890.4327
|
Lenny 
|
Last edited by Lenny77; 09-09-09 at 11:05.
|
09-09-09, 11:06
|
|
Registered User
|
|
Join Date: May 2003
Location: USA
Posts: 5,196
|
|
|
__________________
M. A. Feldman
IBM Certified DBA on DB2 for Linux, UNIX, and Windows
IBM Certified DBA on DB2 for z/OS and OS/390
|
|
09-09-09, 11:11
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
|
|
Quote:
|
Originally Posted by Marcus_A
Needs to be an UDF.
|
I am 100% agree with you.
Everybody can make UDF using this query.
It' s very usable in reports. Users appreciate it.
Lenny 
|
|
09-09-09, 11:57
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
modified formula
I have modified the previous formula because I did not like the results where
number < 1 (0.5 or .5 formatted as .5 which is also not bad, but I prefer to
have the result 0.5
Now query looks like:
Code:
with input (number) as
(select -123456789.012345 from sysibm.sysdummy1
)
,
transform_1 (num, part, rem, trans, fract, k ) as
(select number, int(0), int(abs(number)), varchar('', 150),
abs(number) - int(abs(number)), 12
from input where abs(number) >= 1000
union all
select
number, int(rem / power(10, k - 3)) , mod(rem, power(10, k - 3)),
strip(case when int(rem / power(10, k - 3)) = 0 and trans <= ' ' then ''
when int(rem / power(10, k - 3)) > 0 and trans <= ' ' then
case when number < 0 then '-' else '' end
|| strip(digits(int(rem / power(10, k - 3))), l, '0')
else trans || ',' || substr(digits(int(rem / power(10, k - 3))), 8, 3)
end
|| case when k - 3 = 0 and fract > 0 then strip(varchar(fract), l, '0') else '' end, l, '0')
, fract, k - 3
from transform_1, input
where k - 3 >= 0 and abs(number) >= 1000
union all
select number, int(0), int(0), varchar(number), number, 0
from input
where abs(number) < 1000
)
select num "Input.................",
trans "Result"
from transform_1 tr where k = 0
Lenny 
|
Last edited by Lenny77; 09-09-09 at 14:47.
|
09-09-09, 14:45
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
With this complete formula-SQL we'll get result (for number = -.012345) :
Quote:
Input....... Result
-.012345 -0.012345
|
|
|
09-09-09, 15:23
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Two issues.
1) SQL0347W.
2) It's not work well for negative input.
Code:
------------------------------ Commands Entered ------------------------------
with input (number) as
(select -1123167890.4327 from sysibm.sysdummy1
)
,
transform_1 (num, part, rem, trans, fract, k ) as
(select number, int(0), int(number), varchar('', 150), number - int(number), 12
from input where number > 1
union all
select
number, int(rem / power(10, k - 3)) , mod(rem, power(10, k - 3)),
strip(case when int(rem / power(10, k - 3)) = 0 and trans <= ' ' then ''
when int(rem / power(10, k - 3)) > 0 and trans <= ' ' then
substr(digits(int(rem / power(10, k - 3))), 8, 3)
else trans || ',' || substr(digits(int(rem / power(10, k - 3))), 8, 3)
end
|| case when k - 3 = 0 and fract > 0 then strip(varchar(fract), l, '0') else '' end, l, '0')
, fract, k - 3
from transform_1, input
where k - 3 >= 0 and number > 1
union all
select number, int(0), int(0), varchar(number), number, 0
from input
where number < 1
)
select num, trans from transform_1 tr where k = 0;
------------------------------------------------------------------------------
NUM TRANS
---------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
SQL0347W The recursive common table expression "DB2ADMIN.TRANSFORM_1" may
contain an infinite loop. SQLSTATE=01605
-1123167890.4327 -1123167890.4327
1 record(s) selected with 1 warning messages printed.
|
|
09-09-09, 15:27
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Quote:
|
Originally Posted by tonkuma
Two issues.
1) SQL0347W.
2) It's not work well for negative input.
Code:
------------------------------ Commands Entered ------------------------------
with input (number) as
(select -1123167890.4327 from sysibm.sysdummy1
)
,
transform_1 (num, part, rem, trans, fract, k ) as
(select number, int(0), int(number), varchar('', 150), number - int(number), 12
from input where number > 1
union all
select
number, int(rem / power(10, k - 3)) , mod(rem, power(10, k - 3)),
strip(case when int(rem / power(10, k - 3)) = 0 and trans <= ' ' then ''
when int(rem / power(10, k - 3)) > 0 and trans <= ' ' then
substr(digits(int(rem / power(10, k - 3))), 8, 3)
else trans || ',' || substr(digits(int(rem / power(10, k - 3))), 8, 3)
end
|| case when k - 3 = 0 and fract > 0 then strip(varchar(fract), l, '0') else '' end, l, '0')
, fract, k - 3
from transform_1, input
where k - 3 >= 0 and number > 1
union all
select number, int(0), int(0), varchar(number), number, 0
from input
where number < 1
)
select num, trans from transform_1 tr where k = 0;
------------------------------------------------------------------------------
NUM TRANS
---------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
SQL0347W The recursive common table expression "DB2ADMIN.TRANSFORM_1" may
contain an infinite loop. SQLSTATE=01605
-1123167890.4327 -1123167890.4327
1 record(s) selected with 1 warning messages printed.
|
I am sorry tonkuma !
I just corrected the formula. Please use the last one.
Let me know on issue.
Lenny 
|
|
09-09-09, 15:29
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
|
Corrected Formula
This formula working good for all known to me cases:
Code:
with input (number) as
(select -123456789.012345 from sysibm.sysdummy1
)
,
transform_1 (num, part, rem, trans, fract, k ) as
(select number, int(0), int(abs(number)), varchar('', 150),
abs(number) - int(abs(number)), 12
from input where abs(number) >= 1000
union all
select
number, int(rem / power(10, k - 3)) , mod(rem, power(10, k - 3)),
strip(case when int(rem / power(10, k - 3)) = 0 and trans <= ' ' then ''
when int(rem / power(10, k - 3)) > 0 and trans <= ' ' then
case when number < 0 then '-' else '' end
|| strip(digits(int(rem / power(10, k - 3))), l, '0')
else trans || ',' || substr(digits(int(rem / power(10, k - 3))), 8, 3)
end
|| case when k - 3 = 0 and fract > 0 then strip(varchar(fract), l, '0') else '' end, l, '0')
, fract, k - 3
from transform_1, input
where k - 3 >= 0 and abs(number) >= 1000
union all
select number, int(0), int(0), varchar(number), number, 0
from input
where abs(number) < 1000
)
select num "Input.................",
trans "Result"
from transform_1 tr where k = 0
Lenny
|
|
09-09-09, 15:34
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Still SQL0347W was issued.
(I tested on DB2 9.7 on Windows.)
To supress the warning message, I changed "where k - 3 >= 0 ..." to "where k >= 3 ...".
|
|
09-09-09, 15:42
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
DB2 9.7 for LUW supports VARCHAR_FORMAT ( decimal-floating-point-expression [, format-string] ).
Code:
------------------------------ Commands Entered ------------------------------
connect to SAMPLE ;
------------------------------------------------------------------------------
Database Connection Information
Database server = DB2/NT 9.7.0
SQL authorization ID = DB2ADMIN
Local database alias = SAMPLE
A JDBC connection to the target has succeeded.
------------------------------ Commands Entered ------------------------------
SELECT dec_nbr
, VARCHAR( VARCHAR_FORMAT(dec_nbr, '999,999,999,999.9999'), 30 ) formatted
FROM (VALUES 1123167890.4327, 123167890.4327, 23167890.4327, 3167890.4327, 0.5
, -1123167890.4327, -123167890.4327, -23167890.4327, -3167890.4327, -0.5) s(dec_nbr);
------------------------------------------------------------------------------
DEC_NBR FORMATTED
---------------- ------------------------------
1123167890.4327 1,123,167,890.4327
123167890.4327 123,167,890.4327
23167890.4327 23,167,890.4327
3167890.4327 3,167,890.4327
0.5000 0.5000
-1123167890.4327 -1,123,167,890.4327
-123167890.4327 -123,167,890.4327
-23167890.4327 -23,167,890.4327
-3167890.4327 -3,167,890.4327
-0.5000 -0.5000
10 record(s) selected.
|
|
09-09-09, 15:42
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Ok tonkuma !
In my enviroment it's working with any case.
I tried to use it's working too.
Thanks, Lenny
|
|
09-09-09, 15:45
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Quote:
|
Originally Posted by tonkuma
DB2 9.7 for LUW supports VARCHAR_FORMAT ( decimal-floating-point-expression [, format-string] ).
Code:
------------------------------ Commands Entered ------------------------------
connect to SAMPLE ;
------------------------------------------------------------------------------
Database Connection Information
Database server = DB2/NT 9.7.0
SQL authorization ID = DB2ADMIN
Local database alias = SAMPLE
A JDBC connection to the target has succeeded.
------------------------------ Commands Entered ------------------------------
SELECT dec_nbr
, VARCHAR( VARCHAR_FORMAT(dec_nbr, '999,999,999,999.9999'), 30 ) formatted
FROM (VALUES 1123167890.4327, 123167890.4327, 23167890.4327, 3167890.4327, 0.5
, -1123167890.4327, -123167890.4327, -23167890.4327, -3167890.4327, -0.5) s(dec_nbr);
------------------------------------------------------------------------------
DEC_NBR FORMATTED
---------------- ------------------------------
1123167890.4327 1,123,167,890.4327
123167890.4327 123,167,890.4327
23167890.4327 23,167,890.4327
3167890.4327 3,167,890.4327
0.5000 0.5000
-1123167890.4327 -1,123,167,890.4327
-123167890.4327 -123,167,890.4327
-23167890.4327 -23,167,890.4327
-3167890.4327 -3,167,890.4327
-0.5000 -0.5000
10 record(s) selected.
|
Good to you !
I am working with v.8, where I don't have this function.
Lenny
|
|
09-09-09, 16:03
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Even for guys which are such lucky as tonkuma and can use VARCHAR_FORMAT function it could be interesting because it shown how it works.
Lenny
|
|
09-09-09, 20:32
|
|
Registered User
|
|
Join Date: Jul 2009
Posts: 150
|
|
|
|
09-09-09, 21:08
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Here is Sample UDFs for DB2 UDB V8.
Sample UDFs for Migration
You may be interested in:
FnToChrN.txt TO_CHAR
Returns character string expression of decimal number according to specified format.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Formatting the decimal string
