BUG: SQL SELECT Creates Invalid Numeric Field Length

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

Last Reviewed: July 26, 1995