This has always been a problem using VFP functions in SELECT statements.
Right from day one.
Anything that can return a variable-width value needs to force a
consistent
width. People most often bump into it when Trim() ends up creating a
column
with a width of 0.
Dan
Gene Wirchenko wrote:
> 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.
|
