Microsoft KB Archive/108166
BUG: SQL SELECT Creates Invalid Numeric Field Length
The information in this article applies to:
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.
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.
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
SELECT VAL(STR(lat*long*ytdpurch)) FROM customer INTO TABLE long
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
Last Reviewed: July 26, 1995