IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

A better way to format a Decimal


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Fri Oct 02, 2015 9:15 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Fri Oct 02, 2015 11:22 pm
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Mon Oct 05, 2015 2:32 pm
Reply with quote

Code:
With Mytable(Mydec) as (select 100.29536 from sysibm.sysdummy1)

select Mydec
      ,varchar_format(Mydec,'999.00')
from MyTable
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Wed Oct 07, 2015 8:16 pm
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Populate last day of the Month in MMD... SYNCSORT 2
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts Modifying Date Format Using DFSORT DFSORT/ICETOOL 9
No new posts Need to convert date format DFSORT/ICETOOL 20
No new posts InfoSphere OPTIM CSV ouput vs DSNTIUA... IBM Tools 3
Search our Forums:

Back to Top