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.
|
