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 Formatting using DFsort Guru Nandu DFSORT/ICETOOL 5 Thu Nov 30, 2017 7:33 am Formatting multiple records to a sing... Vignesh Sid SYNCSORT 3 Tue Nov 07, 2017 12:22 pm line 52: Name or string > 250 char... Ashishpanpaliya CLIST & REXX 5 Sat Oct 14, 2017 2:29 am Extend the decimal values in DIVISION Balaryan DFSORT/ICETOOL 3 Thu Oct 05, 2017 4:05 pm Search for a string in many PDS KARTHIGADEVI CLIST & REXX 5 Fri Sep 08, 2017 2:32 pm

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