| |
|
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-30-09, 02:16
|
|
Registered User
|
|
Join Date: Sep 2009
Posts: 2
|
|
|
select query to format a number field to comma seperated
|
|
Hi Team,
Please help me in writing a select query in db2 to format a number field to comma seperated values.
For example: if "AMOUNT = 123456.78", THEN THE SELECT QUERY SHOULD RETURN "AMOUNT = 123,456.78".
Thanks,
Srini
|
|
09-30-09, 03:06
|
|
Registered User
|
|
Join Date: Jul 2006
Location: Pune , India
Posts: 433
|
|
9.7 you have varchar_format
__________________
Rahul Singh
Certified DB2 9 DBA / Application Developer
|
|
09-30-09, 05:36
|
|
Registered User
|
|
Join Date: Sep 2009
Posts: 2
|
|
|
|
Hi Rahul,
Thanks for the post, our db2 version is 9.5, any idea how to implement the same in this version.
Thanks,
Srini
|
|
09-30-09, 05:49
|
|
Registered User
|
|
Join Date: Jul 2009
Posts: 150
|
|
Answer
Quote:
|
Originally Posted by srinivasa.g82
Hi Team,
Please help me in writing a select query in db2 to format a number field to comma seperated values.
For example: if "AMOUNT = 123456.78", THEN THE SELECT QUERY SHOULD RETURN "AMOUNT = 123,456.78".
Thanks,
Srini
|
The answer on your question is here:
Formatting the decimal string
Kara S. 
|
|
09-30-09, 09:56
|
|
Registered User
|
|
Join Date: Jul 2009
Location: NY
Posts: 886
|
|
Extracted Query
You can use extracted from my old topic query:
Code:
With
Input (number) as
(select 123456.78
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-30-09, 21:52
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
In the thread, I wrote:
Quote:
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 ...".
|
And, it will be better to add "number = num" for multiple Input.
Here is a result:
(I added the result of VARCHAR_FORMAT for comparison.
There were two different points.
1) VARCHAR_FORMAT keeps left blanks.
2) VARCHAR_FORMAT keeps integer zero("0") for numbers "ABS(number) < 1.0".)
Code:
------------------------------ Commands Entered ------------------------------
With
Input (number) as
(VALUES
12345678.90, 1234567.89, 123456.78, 12345.67, 1234.56
, 123.45, 12.34, 1.23, 1.0, 0.12, 0.07, 0.0
, -123.45, -12.34, -1.23, -1.0, -0.12, -0.07
)
,
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 number = num
AND k >= 3 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.................",
VARCHAR(VARCHAR_FORMAT(num, '999,999,999.99'), 20) varchar_format,
trans "Result"
from transform_1 tr where k = 0;
------------------------------------------------------------------------------
Input................. VARCHAR_FORMAT Result
---------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
12345678.90 12,345,678.90 12,345,678.90
1234567.89 1,234,567.89 1,234,567.89
123456.78 123,456.78 123,456.78
12345.67 12,345.67 12,345.67
1234.56 1,234.56 1,234.56
123.45 123.45 123.45
12.34 12.34 12.34
1.23 1.23 1.23
1.00 1.00 1.00
0.12 0.12 .12
0.07 0.07 .07
0.00 0.00 .00
-123.45 -123.45 -123.45
-12.34 -12.34 -12.34
-1.23 -1.23 -1.23
-1.00 -1.00 -1.00
-0.12 -0.12 -.12
-0.07 -0.07 -.07
18 record(s) selected.
|
|
10-01-09, 01:17
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
This is an expression generating same formatted strings as VARCHAR_FORMAT().
Code:
------------------------------ Commands Entered ------------------------------
WITH
Input(number) AS (
VALUES
123456789012.34, 12345678901.23, 1234567890.12, 123456789.01
, 12345678.90, 1234567.89, 123456.78, 12345.67, 1234.56
, 123.45, 12.34, 1.23, 1.00, 0.12, 0.07, 0.00
, -123456789012.34, -12345678901.23, -1234567890.12, -123456789.01
, -12345678.90, -1234567.89, -123456.78, -12345.67, -1234.56
, -123.45, -12.34, -1.23, -1.00, -0.12, -0.07
)
SELECT number
, VARCHAR(VARCHAR_FORMAT(number, '999,999,999,999.99'), 30) AS varchar_format
, SUBSTR( ' '
, 1
, 15 - INT(POSSTR(CHAR(ABS(number)),'.')*1.33-0.33)
+ CASE WHEN ABS(number) < 1.0 THEN 0 ELSE 1 END
) ||
CASE WHEN number < 0.0 THEN '-' ELSE ' ' END ||
CASE WHEN ABS(number) < 1.0 THEN '0' ELSE '' END ||
TRANSLATE( SUBSTR('abc,def,ghi,jkl.mn',17 - INT(POSSTR(CHAR(ABS(number)),'.')*1.33-0.33))
, CHAR(ABS(number))
, SUBSTR('abcdefghijkl.mn',14 - POSSTR(CHAR(ABS(number)),'.'))
) AS case_and_translate
FROM Input
ORDER BY
number DESC
;
------------------------------------------------------------------------------
NUMBER VARCHAR_FORMAT CASE_AND_TRANSLATE
---------------- ------------------------------ -----------------------------------
123456789012.34 123,456,789,012.34 123,456,789,012.34
12345678901.23 12,345,678,901.23 12,345,678,901.23
1234567890.12 1,234,567,890.12 1,234,567,890.12
123456789.01 123,456,789.01 123,456,789.01
12345678.90 12,345,678.90 12,345,678.90
1234567.89 1,234,567.89 1,234,567.89
123456.78 123,456.78 123,456.78
12345.67 12,345.67 12,345.67
1234.56 1,234.56 1,234.56
123.45 123.45 123.45
12.34 12.34 12.34
1.23 1.23 1.23
1.00 1.00 1.00
0.12 0.12 0.12
0.07 0.07 0.07
0.00 0.00 0.00
-0.07 -0.07 -0.07
-0.12 -0.12 -0.12
-1.00 -1.00 -1.00
-1.23 -1.23 -1.23
-12.34 -12.34 -12.34
-123.45 -123.45 -123.45
-1234.56 -1,234.56 -1,234.56
-12345.67 -12,345.67 -12,345.67
-123456.78 -123,456.78 -123,456.78
-1234567.89 -1,234,567.89 -1,234,567.89
-12345678.90 -12,345,678.90 -12,345,678.90
-123456789.01 -123,456,789.01 -123,456,789.01
-1234567890.12 -1,234,567,890.12 -1,234,567,890.12
-12345678901.23 -12,345,678,901.23 -12,345,678,901.23
-123456789012.34 -123,456,789,012.34 -123,456,789,012.34
31 record(s) selected.
|
Last edited by tonkuma; 10-01-09 at 08:12.
|
| 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
>
select query to format a number field to comma seperated
