My boss uncovered a nasty bug in a report. 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.

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:

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:

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:

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:

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:

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:

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:

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:

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: