Re: %EDITC function in SQL?

This is a multipart message in MIME format.
--
[ Picked text/plain from multipart/alternative ]
Before I did that I would do one of the following:
1)  See if the char() function was good enough.
2)  Define a user defined function.

The user defined function could be written with 1 line of C specs in
RPGLE.  Granted the PR's and PI's will add a few more lines.

Rob Berendt
--
"They that can give up essential liberty to obtain a little temporary
safety deserve neither liberty nor safety."
Benjamin Franklin




Vernon Hamberg <vhamberg@attbi.com>
Sent by: midrange-l-admin@midrange.com
03/28/2002 11:06 AM
Please respond to midrange-l


        To:     midrange-l@midrange.com
        cc:
        Fax to:
        Subject:        Re: %EDITC function in SQL?


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.

_______________________________________________
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.