RE: Suppress zeros in Query Manager???

["Tyler, Matt" <mattt@xxxxxxxxxxxxxx>]

Rich,
        I tried this and I agree with you about not being able to with edit
codes.  What you want to do depends on the amount of work you want to put
into the query and how complicated it can be.  I often need to format a UPC
(w/o DDS editing).  I will convert the number into character string,
sub-string and concatenate the formatting characters together with the
number.

The following statement;
SUBSTR(DIGITS(UPC),1,5) || '-' || SUBSTR(DIGITS(UPC),4,5) ||'-'||
SUBSTR(DIGITS(UPC),5,5) as UPC
will result in a UPC (000005100000011) looking like 00000-51000-00011.

I use DIGITS() since it does not suppress leading zeros like CHAR() does
(except if the number is zero value then one zero remains).

If you need to suppress leading zeros you can use the STRIP() function:
STRIP(STRIP(STRIP(
SUBSTR(DIGITS(UPC),1,5) || '-' || SUBSTR(DIGITS(UPC),6,5) ||'-'||
SUBSTR(DIGITS(UPC),11,5), L,'0'),L,'-'),L,'0') AS upc
This however zero suppresses but left justifies.

I am sure there are other ways to do the same thing but it would have been
best to make IBM give us the ability to edit data on reports easier.  One
other solution I just thought of is to output a QMQRY to an out-file and
then use the out-file as the source for a QRYDFN, where you can edit data
tons better.


Thank you,
Matt Tyler
Mattt@wincofoods.com

-----Original Message-----
From: rich herdman [mailto:rherdman@columbus.rr.com]
Sent: Friday, September 27, 2002 07:50
To: midrange-l@midrange.com
Subject: Suppress zeros in Query Manager???

The subject says it all - does anyone know how to suppress zeros in a Query
Manager
form?  I'm on V5R1, and the available edit codes won't do it.



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.