Re: %EDITC function in SQL?

[Vernon Hamberg <vhamberg@xxxxxxxxx>]

OOH - what a challenge! Try this if you are post v4r3 (?)

select
     case
       when f2i > 999999999 then
         substr(digits(f2i), 1, 1) || ',' ||
         substr(digits(f2i), 2, 3) || ',' ||
         substr(digits(f2i), 5, 3) || ',' ||
         substr(digits(f2i), 8, 3)
       when f2i > 999999 then
         strip(substr(digits(f2i), 2, 3) || ',' ||
               substr(digits(f2i), 5, 3) || ',' ||
               substr(digits(f2i), 8, 3), l, '0')
       when f2i > 999 then
         strip(substr(digits(f2i), 5, 3) || ',' ||
               substr(digits(f2i), 8, 3), l, '0')
       else
         strip(substr(digits(f2i), 8, 3), l, '0')
     end
from vern/temp

f2i is an INTEGER type and DIGITS is, hence, 10 characters long.

At 09:19 AM 3/28/02 -0500, you wrote:
>Does anyone know of a way to format a number in an SQL statement (similar
>to the %EDITC BIF in RPG4)?
>
>
>Here is what I am trying to do:  I am using SQL to insert records into a file,
>and then emailing the contents of that file to various people.  Here
>is my SQL statement:
>
>"INSERT INTO QTEMP/MSG
>select 'Records in file:  ' || DIGITS(count(*)) from lib/file"
>
>
>Here's the record it write to my file:
>
>"Records in file:  0000011862"
>
>
>Is there any way to do some formatting to that number?  I'd like
>to remove the leading zeros at a minimum, and inserting commas would
>be helpful.  Any suggestions?
>
>Rich Herdman
>Abbott Foods
>Columbus, OH
>email: rherdman@abbottfoods.com
>
>
>_______________________________________________
>This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
>To post a message email: MIDRANGE-L@midrange.com
>To subscribe, unsubscribe, or change list options,
>visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
>or email: MIDRANGE-L-request@midrange.com
>Before posting, please take a moment to review the archives
>at http://archive.midrange.com/midrange-l.