Re: No Leading Zero's when using Digits function in Query

[CRPence <CRPbottle@xxxxxxxxx>]

  And for a SQL expression such as the CHAR(mynum) to become available 
within a Query Definition (*QRYDFN), encapsulate the expression in a SQL 
VIEW, and then specify that SQL VIEW as a /file selection/ for the query 
instead of the file with the column mynum.

  For example, to get the CDTLMT column of the file QCUSTCDT in QIWS to 
become available as the expression CHAR(CDTLMT), specify the SQL VIEW 
QTEMP/MyCust as file selection for the query, instead of QIWS/QCUSTCDT, 
after creating that SQL VIEW with the following CREATE statement:

   create view qtemp/MyCust as
   ( select a.* , char(CDTLMT) as CDTLMTc
     from qiws/qcustcdt as a )

  The above CREATE VIEW enables the original column to exist unmodified 
from its original record format, so as not to impact other desirable 
references to the numeric column [as numeric] in the query definition, 
and avoids having to specify a column-list to name the columns.

  Note that the following expressions are also available, to cast the 
result as a varying character instead of a fixed-length character, which 
might be preferable if the origin of the desire is to concatenate the 
/edited/ numeric value with other character data:

   VARCHAR ( CDTLMT )

   TRIM ( LEADING '0' FROM DIGITS ( CDTLMT ) )

  Note: The last expression with TRIM and DIGITS may be more desirable, 
if there is decimal scale and\or sign being eliminated by the current 
[attempted] use of DIGITS; i.e. both CHAR and VARCHAR include decimal 
point [albeit optional second argument can specify comma or period] and 
leading negative sign.

Regards, Chuck

On 07 Mar 2013 15:00, DeLong, Eric wrote:
> In SQL, just use CHAR( mynum )...
>
> In Query, just change the numeric edit code used for the field.
>
> Kirk Goins on Thursday, March 07, 2013 4:30 PM wrote:
> > Is it possible from within Query to convert a numeric field to
> > char without leading zeroes? DIGITS puts leading zeroes in the
> > char field.