It is normal, or at least consistent. From the Ver 9 SP 2 docs for the
REPLACE command:
"When the expression value is longer than the width of a numeric field,
REPLACE forces the value to fit by carrying out the following steps:
*
First, REPLACE truncates decimal places and rounds the remaining
decimal ****tion of the field.
*
If the value still doesn't fit, REPLACE stores the field contents
using scientific notation.
*
If the value still doesn't fit, REPLACE replaces the field
contents with asterisks."
And so Fox allows "oversized" values to be im****ted, but doesn't let you
edit them into an over length value. The record with the '2.333' value
should display and allow editing for all three places. And if you
change the '555.36' to '55.36' the format will irrevocably revert to 2.3
form; that is ' 55.360' will be displayed and you can't edit it back to
3.2 form.
- Rush
Tom Libby wrote:
> What is the field definition for the field that is overflow? If I run
the
> below in VFP 7 SP2 and VFP 9 SP2 the field definition in curTmp3 for f2
is
> N(6,3).
>
> If I change f2 to integer in curTmp2 then in curTmp3 the field is
N(11,0).
>
> This I thought was odd 555.355 for row "C" is 555.36 and I cannot enter
> three decimal places in the table, is this normal behavior? I do not
think I
> have seen that before in my 15 years of using FoxPro.
>
> CREATE CURSOR curTmp1 (f1 C(1))
> SELECT 0
> CREATE CURSOR curTmp2(f1 C(1), f2 N(6,3))
>
> INSERT INTO curTmp1 (f1) VALUES("A")
> INSERT INTO curTmp1 (f1) VALUES("B")
> INSERT INTO curTmp1 (f1) VALUES("C")
>
> INSERT INTO curTmp2 (F1,F2) values("B",2.333)
> INSERT INTO curTmp2 (F1,F2) values("C",555.355)
>
> SELECT curTmp1.f1,NVL(curTmp2.f2,0) as f2 ;
> FROM curTmp1 LEFT OUTER JOIN curTmp2 ON curTmp1.f1 == curTmp2.f1 ;
> INTO CURSOR curTmp3 READWRITE
>
>
> "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.
>>
>
>
>
|
