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

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.