VFP9 SQL Query Column Width Bites Again!

#41

"Gene Wirchenko" <genew (AT) ocis (DOT) net> wrote

Quote:

Jeroen van Kalken wrote:

On Thu, 08 May 2008 14:08:56 -0700, Gene Wirchenko <genew (AT) ocis (DOT) 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

--

Quote:

\_/| ------ ProLib - programmers liberty -----------------
(.. ) Our MVPs and MCPs make the Fox run....

- / See us at www.prolib.de or www.AFPages.de
-----------------------------------------------------------

#42

"Gene Wirchenko" <genew (AT) ocis (DOT) net> wrote

Quote:

Jeroen van Kalken wrote:

On Thu, 08 May 2008 14:08:56 -0700, Gene Wirchenko <genew (AT) ocis (DOT) 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

--

Quote:

\_/| ------ ProLib - programmers liberty -----------------
(.. ) Our MVPs and MCPs make the Fox run....

- / See us at www.prolib.de or www.AFPages.de
-----------------------------------------------------------

#43

"Gene Wirchenko" <genew (AT) ocis (DOT) net> wrote

Quote:

Jeroen van Kalken wrote:

On Thu, 08 May 2008 14:08:56 -0700, Gene Wirchenko <genew (AT) ocis (DOT) 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

--

Quote:

\_/| ------ ProLib - programmers liberty -----------------
(.. ) Our MVPs and MCPs make the Fox run....

- / See us at www.prolib.de or www.AFPages.de
-----------------------------------------------------------

#44

"Gene Wirchenko" <genew (AT) ocis (DOT) net> wrote

Quote:

Jeroen van Kalken wrote:

On Thu, 08 May 2008 14:08:56 -0700, Gene Wirchenko <genew (AT) ocis (DOT) 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

--

Quote:

\_/| ------ ProLib - programmers liberty -----------------
(.. ) Our MVPs and MCPs make the Fox run....

- / See us at www.prolib.de or www.AFPages.de
-----------------------------------------------------------

#45

"Gene Wirchenko" <genew (AT) ocis (DOT) net> wrote

Quote:

Jeroen van Kalken wrote:

On Thu, 08 May 2008 14:08:56 -0700, Gene Wirchenko <genew (AT) ocis (DOT) 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

--

Quote:

\_/| ------ ProLib - programmers liberty -----------------
(.. ) Our MVPs and MCPs make the Fox run....

- / See us at www.prolib.de or www.AFPages.de
-----------------------------------------------------------

#46

"Gene Wirchenko" <genew (AT) ocis (DOT) net> wrote

Quote:

Jeroen van Kalken wrote:

On Thu, 08 May 2008 14:08:56 -0700, Gene Wirchenko <genew (AT) ocis (DOT) 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

--

Quote:

\_/| ------ ProLib - programmers liberty -----------------
(.. ) Our MVPs and MCPs make the Fox run....

- / See us at www.prolib.de or www.AFPages.de
-----------------------------------------------------------

#47

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 (AT) ocis (DOT) net> wrote

Quote:

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.

#48

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 (AT) ocis (DOT) net> wrote

Quote:

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.

#49

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 (AT) ocis (DOT) net> wrote

Quote:

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.

#50

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 (AT) ocis (DOT) net> wrote

Quote:

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.