On Thu, 08 May 2008 14:08:56 -0700, Gene Wirchenko <genew@[EMAIL PROTECTED]
>
wrote:
in VFP9 you can also use the CAST function to get the right amount of
digits; making it slightly more readable/understandable.
Like: Cast(nvl(pcl.cpcl,0) as n(10,1))
> 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.
|
