VFP9 SQL Query Column Width Bites Again!

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.

Dan
Thu May 08 16:17:31 CDT 2008

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

Jeroen
Thu May 08 18:25:10 CDT 2008

On Thu, 08 May 2008 14:08:56 -0700, Gene Wirchenko <genew@ocis.net>
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 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.

John
Thu May 08 18:41:18 CDT 2008

I have a standard method that I always use to convert numeric to character.
It accepts the value to be converted, width to be returned and decimals to
be used in formatting.
The code is below as a sample.

Hope it helps someone.

John Pugh
Adelaide, South Australia

* Convert a number to character for display - handles decimals

* Always include the first optional parameter (width) when the result will

* ...be used to generate a cursor to prevent the width of the first record

* ...determining the width of the field in the cursor

* Sample call:=

*!* lc_test=oUtil.n2c(ln_num[,14,4])

*!* LPARAMETERS tn_number,tn_pad,tn_decimals, tg_no_commas

LPARAMETERS tn_number,tn_pad,tn_decimals,tg_no_commas

LOCAL lc_return,lc_temp,ln_decimals

lc_return=''

DO CASE

* Handle the possibility of SQL returning .null

CASE ISNULL(tn_number) OR EMPTY(tn_number)

lc_return=''

CASE tg_no_commas=.t. AND tn_decimals=0 && no commas only needed with no
decimals

lc_return=ALLTRIM(TRANSFORM(tn_number,'999999999'))


CASE PARAMETERS()=3 AND tn_decimals=0 && Specify 0 to return no decimals

lc_return=ALLTRIM(TRANSFORM(tn_number,'999,999,999'))

CASE EMPTY(tn_decimals) OR tn_decimals=2

lc_return=ALLTRIM(TRANSFORM(tn_number,'999,999,999.99'))

CASE tn_decimals=1

lc_return=ALLTRIM(TRANSFORM(tn_number,'999,999,999.9'))

CASE tn_decimals=3

* TRANSFORM uses SET DECIMALS setting

ln_decimals=SET('decimals')

SET DECIMALS TO 3

lc_return=ALLTRIM(TRANSFORM(tn_number,'999,999,999.999'))

SET DECIMALS TO ln_decimals

CASE tn_decimals=4

ln_decimals=SET('decimals')

SET DECIMALS TO 4

lc_return=ALLTRIM(TRANSFORM(tn_number,'999,999,999.9999'))

SET DECIMALS TO ln_decimals

OTHERWISE

WAIT WINDOW 'oUtil.n2c handles up to 4 decimals - it was asked to handle
'+TRANSFORM(tn_decimals)

lc_return=''

ENDCASE

IF NOT EMPTY(tn_pad) && Pad for use in cursors

lc_return=PADL(lc_return,tn_pad,' ')

ENDIF

RETURN lc_return

"Gene Wirchenko" <genew@ocis.net> wrote in message
news:l0q624l380i9ljdis0dq3jkf6rm4lhj450@4ax.com...
> 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.

Gene
Thu May 08 20:02:40 CDT 2008

Jeroen van Kalken <I@dont.like.spam> wrote:

>On Thu, 08 May 2008 14:08:56 -0700, Gene Wirchenko <genew@ocis.net>
>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))

This is getting messier. It appears that I will be adjusting a
lot of code.

Is case() ever necessary when a column is being used directly?
i.e.
select thiscol,thatcol from ...
I am hoping that such usage is always safe.

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

Stefan
Fri May 09 00:47:48 CDT 2008


"Gene Wirchenko" <genew@ocis.net> wrote in message
news:hg87241haega7mpeje3tf2o1n56og7u520@4ax.com...
> Jeroen van Kalken <I@dont.like.spam> wrote:
>
>>On Thu, 08 May 2008 14:08:56 -0700, Gene Wirchenko <genew@ocis.net>
>>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))
>
> This is getting messier. It appears that I will be adjusting a
> lot of code.
> Is case() ever necessary when a column is being used directly?
> i.e.
> select thiscol,thatcol from ...
> I am hoping that such usage is always safe.

Right, Cast() is useful for intentional type conversions and for
"padding" calculated columns, so in an average SQL field list
it might normally be an exception.




hth
-Stefan




--
|\_/| ------ ProLib - programmers liberty -----------------
(.. ) Our MVPs and MCPs make the Fox run....
- / See us at www.prolib.de or www.AFPages.de
-----------------------------------------------------------

Tom
Fri May 09 08:26:53 CDT 2008

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@ocis.net> wrote in message
news:l0q624l380i9ljdis0dq3jkf6rm4lhj450@4ax.com...
> 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.

Rush
Fri May 09 09:56:19 CDT 2008

Gene Wirchenko wrote:
> Jeroen van Kalken <I@dont.like.spam> wrote:
>
>
>> On Thu, 08 May 2008 14:08:56 -0700, Gene Wirchenko <genew@ocis.net>
>> 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))
>>
>
> This is getting messier. It appears that I will be adjusting a
> lot of code.
>
> Is case() ever necessary when a column is being used directly?
> i.e.
> select thiscol,thatcol from ...
> I am hoping that such usage is always safe.
>
Yes, you are safe in directly using existing columns.

- Rush

Bernhard
Fri May 09 10:11:17 CDT 2008

Hi Tom,

> 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 curTmp2(f1 C(1), f2 N(6,3))
>
> INSERT INTO curTmp2 (F1,F2) values("C",555.355)

That's the way foxpro (may be also ancient dBase/foxbase) handles numeric data
when it exceeds the defined field size.
You insert a number that is larger than the definition allows, since it has 7
places. So foxpro stores the number and cuts (rounds) the decimal places but
keeps the places before the decimal point. If you
INSERT INTO curTmp2 (F1,F2) values("C",-555.355)
then it would store -555.4
Other DB systems would throw an error in both cases.

Regards
Bernhard Sander

Rush
Fri May 09 10:25:53 CDT 2008

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 portion 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 imported, 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@ocis.net> wrote in message
> news:l0q624l380i9ljdis0dq3jkf6rm4lhj450@4ax.com...
>
>> 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.
>>
>
>
>

Dan
Fri May 09 10:43:16 CDT 2008

Gene Wirchenko wrote:
> Jeroen van Kalken <I@dont.like.spam> wrote:
>
>> On Thu, 08 May 2008 14:08:56 -0700, Gene Wirchenko <genew@ocis.net>
>> 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))
>
> This is getting messier. It appears that I will be adjusting a
> lot of code.
>
> Is case() ever necessary when a column is being used directly?
> i.e.
> select thiscol,thatcol from ...
> I am hoping that such usage is always safe.

Yes, a single column should be safe. They'll always all be the same width,
by definition.

Dan