I have a standard method that I always use to convert numeric to character.
It accepts the value to be converted, width to be returned and decimals to
be used in formatting.
The code is below as a sample.
Hope it helps someone.
John Pugh
Adelaide, South Australia
* Convert a number to character for display - handles decimals
* Always include the first optional parameter (width) when the result will
* ...be used to generate a cursor to prevent the width of the first record
* ...determining the width of the field in the cursor
* Sample call:=
*!* lc_test=oUtil.n2c(ln_num[,14,4])
*!* LPARAMETERS tn_number,tn_pad,tn_decimals, tg_no_commas
LPARAMETERS tn_number,tn_pad,tn_decimals,tg_no_commas
LOCAL lc_return,lc_temp,ln_decimals
lc_return=''
DO CASE
* Handle the possibility of SQL returning .null
CASE ISNULL(tn_number) OR EMPTY(tn_number)
lc_return=''
CASE tg_no_commas=.t. AND tn_decimals=0 && no commas only needed with no
decimals
lc_return=ALLTRIM(TRANSFORM(tn_number,'999999999'))
CASE PARAMETERS()=3 AND tn_decimals=0 && Specify 0 to return no decimals
lc_return=ALLTRIM(TRANSFORM(tn_number,'999,999,999'))
CASE EMPTY(tn_decimals) OR tn_decimals=2
lc_return=ALLTRIM(TRANSFORM(tn_number,'999,999,999.99'))
CASE tn_decimals=1
lc_return=ALLTRIM(TRANSFORM(tn_number,'999,999,999.9'))
CASE tn_decimals=3
* TRANSFORM uses SET DECIMALS setting
ln_decimals=SET('decimals')
SET DECIMALS TO 3
lc_return=ALLTRIM(TRANSFORM(tn_number,'999,999,999.999'))
SET DECIMALS TO ln_decimals
CASE tn_decimals=4
ln_decimals=SET('decimals')
SET DECIMALS TO 4
lc_return=ALLTRIM(TRANSFORM(tn_number,'999,999,999.9999'))
SET DECIMALS TO ln_decimals
OTHERWISE
WAIT WINDOW 'oUtil.n2c handles up to 4 decimals - it was asked to handle
'+TRANSFORM(tn_decimals)
lc_return=''
ENDCASE
IF NOT EMPTY(tn_pad) && Pad for use in cursors
lc_return=PADL(lc_return,tn_pad,' ')
ENDIF
RETURN lc_return
"Gene Wirchenko" <genew@[EMAIL PROTECTED]
> wrote in message
news:l0q624l380i9ljdis0dq3jkf6rm4lhj450@[EMAIL PROTECTED]
> My boss uncovered a nasty bug in a re****t. I have found where
> the error occurs. What I did not understand is WHY it happens.
> Partway through writing this up, it occurred to me why. I thought I
> would pass it along.
>
> The summary: nvl() can determine the width of the column. Pad
> the second parameter as needed (000000 vs. 0) to avoid narrow columns.
>
> Here is the nasty query:
>
> SQLSEL;
> wol.wonbr,wol.wccode,wol.trndtlow,wol.trndthi,;
> nvl(pcl.cpcl,0) as cpcl,nvl(pcl.tweight,0) as tweight,;
> nvl(con.contot,0) as contot,nvl(alc.alctot,0) as alctot,;
> nvl(dsb.dsbtot,0) as dsbtot,nvl(dsb.dsboh,0) as dsboh;
> from (alwolist) as wol;
> left outer join (alpcl) as pcl on pcl.wonbr=wol.wonbr;
> left outer join (alcontot) as con on con.wonbr=wol.wonbr;
> left outer join (alalctot) as alc on alc.wonbr=wol.wonbr;
> left outer join (aldsbamts) as dsb on dsb.wonbr=wol.wonbr;
> into cursor (alg1) nofilter
>
> SQLSEL is simply a #define of "select" so that I can distinguish
> between SQL selects and work area selects. Each of the cursors
> referred to exists.
>
> There might not be be data for each possibility of wonbr. That
> is why the left joins in the first place. If there is a null, I want
> a zero to replace it.
>
> There is no data for 2006. If a start date of 2007 or later is
> chosen, the query works. If the a start of 2006 or earlier is chosen,
> the query goes screwy. The nvl() work fine for the 2006 wonbr values,
> but the later one get asterisks (overflow).
>
> The cause of this is the column is too narrow (just one digit
> wide). I dealt with this by changing the zero literals to reflect the
> maximum size, so
> nvl(pcl.tweight,000000.0) as tweight
> and so forth.
>
> I am going to be changing a lot of nvl() parameters.
>
> Sincerely,
>
> Gene Wirchenko
>
>
> Computerese Irregular Verb Conjugation:
> I have preferences.
> You have biases.
> He/She has prejudices.
|
