Registrations are now open. Join us today!
There is still a lot of work to do on the wiki yet! More information about editing can be found here.
Already have an account?

Microsoft KB Archive/108166

From BetaArchive Wiki

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


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

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
© 1999 Microsoft Corporation. All rights reserved. Terms of Use.