Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref

Author Message
lkhiger

New User

Joined: 28 Oct 2005
Posts: 89

Posted: Sat Sep 12, 2009 7:23 pm    Post subject: Formatting the decimal string

The following query give you the easiest way to format the Decimal Number to 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(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

 Quote: Input............................ Result -1123167890.4327 -1,123,167,890.4327

Thanks, Lenny

lkhiger

New User

Joined: 28 Oct 2005
Posts: 89

 Posted: Sat Sep 12, 2009 7:30 pm    Post subject: You can make UDF based on this query DB2 V9 supports VARCHAR_FORMAT ( decimal-floating-point-expression [, format-string] ). All users of V9 can use my query for learning what inside. Any others, which doesn't have V9, yet, can make UDF based on this query. Lenny
 All times are GMT + 6 Hours
 Page 1 of 1

Search our Forum:

 Topic Author Forum Replies Posted Similar Topics How can I convert charactor to fixed ... liying DFSORT/ICETOOL 7 Tue May 22, 2018 3:35 pm Find & Replace string in CA-SORT mrgnndhmk CA Products 1 Fri Mar 30, 2018 12:58 am How to group and sum the decimal value sakrat DFSORT/ICETOOL 2 Tue Mar 06, 2018 9:13 pm Find a string in PS file opened in br... mukkas CLIST & REXX 3 Sat Feb 24, 2018 2:15 pm Formatting using DFsort Guru Nandu DFSORT/ICETOOL 5 Thu Nov 30, 2017 7:33 am

 © 2003-2017 IBM MAINFRAME Software Support Division
 Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us