Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

A better way to format a Decimal

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1696
Location: NY,USA

PostPosted: Fri Oct 02, 2015 9:15 pm    Post subject: A better way to format a Decimal
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

Senior Member


Joined: 21 Sep 2010
Posts: 1696
Location: NY,USA

PostPosted: Fri Oct 02, 2015 11:22 pm    Post subject:
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    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1696
Location: NY,USA

PostPosted: Wed Oct 07, 2015 8:16 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Format record to remove Leading zeroes Learncoholic DFSORT/ICETOOL 14 Wed Apr 05, 2017 2:43 pm
No new posts How to pick only YYMMDD from DATE1P (... atulbaviskar SYNCSORT 7 Wed Mar 22, 2017 11:39 am
No new posts Date and time format in CICS Chandru3183 CICS 2 Sat Mar 18, 2017 12:46 pm
No new posts Joinkeys with decimal scdinesh DFSORT/ICETOOL 3 Fri Feb 10, 2017 4:46 am
No new posts Converting decimal to numeric using I... Balaryan DFSORT/ICETOOL 4 Thu Feb 02, 2017 11:27 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us