View previous topic :: View next topic
|
Author |
Message |
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
I have to format a Decimal (8,5) to a nicely formated data.
e.g.
Code: |
case-1
In DB2
100.29536
Output
100.30
case-2
100.00000
output
100
case-3
000.88
output
0.88 |
I am using the following and I feel this option has too much of hardcoding though it is giving me the right results.
Code: |
select
case when substr(char(100.29536),6,2) = '00'
then strip(char(cast(ROUND (100.29536,2) as int)))
when substr(STRIP(LTRIM(CHAR(ROUND(100.29536,2))),B,'0'),1,1) = '.'
then STRIP(LTRIM(CHAR(ROUND(100.29536,2))),T,'0')
else STRIP(LTRIM(CHAR(ROUND(100.29536,2))),B,'0')
end
from T1 |
So I trying to see if there is any direct fuction that can be used or atleast to get no hardcoding done. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
I have came up with this and the use is okay with the data quality.
Code: |
strip(ltrim(char(decimal(ROUND(100.000,2),8,2))),l,'0') |
few of the above cases wouldn't fulfill but fortunately with this data nothing is broken.
Still if anyonehas a better way to tackle such data then I open to see and accomodate the chagnge. |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
Code: |
With Mytable(Mydec) as (select 100.29536 from sysibm.sysdummy1)
select Mydec
,varchar_format(Mydec,'999.00')
from MyTable |
|
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
Thanks..
We are still on V10.0.0 compatibility mode and it also seems the new function mode is disabled for these scalar functions.
Code: |
ATTEMPT TO USE NEW FUNCTION BEFORE NEW FUNCTION MODE. SQLCODE=-4700, SQLSTATE=56038, DRIVER=4.14.113 |
|
|
Back to top |
|
|
|