Microsoft KB Archive/108166

{| = BUG: SQL SELECT Creates Invalid Numeric Field Length =
 * width="100%"|

ID: Q108166

The information in this article applies to:


 * Microsoft FoxPro for Windows, versions 2.5, 2.5a, 2.5b, 2.6
 * Microsoft FoxPro for MS-DOS, versions 2.5, 2.5a, 2.5b, 2.6

SYMPTOMS
An SQL SELECT statement can create an invalid field length for a numeric field when calculations are done with large numbers. No error is returned; however, in further commands (for example, UNION), errors might arise because the maximum size for a numeric field is 20.

STATUS
Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

WORKAROUND
Use the VAL and STR functions on the field being created. For example, in the Steps to Reproduce Problem section below, replace the line

SELECT lat*long*ytdpurch FROM customer INTO TABLE long with SELECT VAL(STR(lat*long*ytdpurch)) FROM customer INTO TABLE long

MORE INFORMATION
Steps to Reproduce Problem

The following code will create an invalid field:

CREATE TABLE customer ; ( cno C(5), company C(35), contact C(20), ;     address C(30), city C(15), state C(2), zip C(5), ;      phone C(12), ono C(1), ytdpurch N(8,2), lat N(7,4), ;      long N(8,4) )

INSERT INTO customer (cno, company, contact, address, city, state,;     zip, phone, ono, ytdpurch, lat, long) ; VALUES ('a123', 'Company', 'Name', 'Street', ;     'City', 'WA', '12345', '1206123456', '1', ;      10000.99, 10000.999, 10000.999)

SELECT lat*long*ytdpurch FROM customer INTO TABLE long DISPLAY STRUCTURE This SQL SELECT statement will create a 23-digit long numeric field. Nowhere else in FoxPro is it possible to create a numeric field longer than 20 digits in a table. Additional reference words: FoxDos FoxWin 2.50 2.50a 2.50b buglist2.50 buglist2.50a buglist2.50b numeric max length size 2.60 buglist2.60 KBCategory: kbprg kbbuglist KBSubcategory: FxprgSql
 * }