Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2004
    Posts
    45

    Unanswered: Numeric Value Out Of Range

    I am trying to simply execute an sql query, but when I try to get an integer field, I get a Numeric Value out of range error.

    SELECT OrderNumber, ClientCode, SalesAmount FROM TDATA WHERE OrderNumber = 'P12345'

    SalesAmount is the numeric field, the statement works fine if I take out that field. The same error is encountered for other fields of the same type. Is the syntax wrong??

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Sounds like you've got some bad data in the table. Does the "OrderNumber" uniquely identify the record? If not, what happens if you restrict the result set to one record? Also, what tool are you using to execute the query? Does the database pass a Database Consistency test?
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    Jul 2004
    Posts
    45
    "OrderNumber" is the unique key. I am using the pervasive control center to execute the query, but any other script (ultimately I am trying to use the DSN through ASP) generates the same error.

    The table fails the consistency test with the following results...keep in mind I did not design the database or tables, rather they are from a commercial program. What I'm doing w/ them is beyond their intended purpose. BOth the data files and DDFs were designed by the software vendor.

    ===============================================

    Database consistency test results.

    Database Location: D:\DB\TEDATA
    ===============================================
    Database Check Statistics
    ===============================================

    Pervasive.SQL Compatibility Mode = 7
    Tables Checked = 1
    Tables Passed = 0
    Tables Failed = 1

    Illegal Object Names = 20
    Record Length Inconsistencies = 2
    Variable Length Column Inconsistencies = 0
    Data Type Inconsistencies = 0
    Column Overlaps = 0
    Full Path (instead of Relative) Table Locations = 0
    Table Files Don't Exist = 0
    Index Inconsistencies = 1
    System Table Inconsistencies = 0
    General Dictionary Inconsistencies = 0
    ===============================================

    Problems Detected with Database Definitions

    ===============================================

    +++++++++++++++++++++++++++++++++++++++++++++++

    Table Name = TWDATA

    illegal column name '1099Status'.

    illegal column name '1001Months'.

    illegal column name '1001PerMonth'.

    illegal column name '1002Months'.

    illegal column name '1002PerMonth'.

    illegal column name '1003Months'.

    illegal column name '1003PerMonth'.

    illegal column name '1004Months'.

    illegal column name '1004PerMonth'.

    illegal column name '1005Months'.

    illegal column name '1005PerMonth'.

    illegal column name '1006Desc'.

    illegal column name '1006Months'.

    illegal column name '1006PerMonth'.

    illegal column name '1007Desc'.

    illegal column name '1007Months'.

    illegal column name '1007PerMonth'.

    illegal column name '1008Desc'.

    illegal column name '1008Months'.

    illegal column name '1008PerMonth'.

    Fixed Record Length Mismatch.

    Dictionary indicates length 5246

    Data file indicates length 21.

    Variable Data Type Column Mismatch.

    The Data file indicates that a variable length column exists.

    The Dictionary does not.

    The Data File Index is Not Defined in the Dictionary.

    The data file defines index 0 seg 0

    The dictionary does not.

    ===============================================

  4. #4
    Join Date
    Jul 2004
    Posts
    45
    Another quick note...if the value is 0 for the SalesAmount field, it seems to work fine and gives the 0 value. Only when it has a non-zero value does it throw the error.

  5. #5
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    These inconsitencies:
    Fixed Record Length Mismatch.
    Dictionary indicates length 5246
    Data file indicates length 21.
    and
    Variable Data Type Column Mismatch.
    The Data file indicates that a variable length column exists.
    The Dictionary does not.

    are going to cause all kinds of problems. Because these are a third party app and DDFs, contact the vendor and get accurate DDFs.
    The problem is most likely that the app vendor took a short cut and is storing a significant portion of the data in a variable length field that actually has a fixed structure. The DDFs try to define all of that data as a fixed length record rather than a variable length field.
    Unfortunately, without accurate DDFs, this may never work and isn't a problem with Pervasive but a problem with the database design.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  6. #6
    Join Date
    Jul 2004
    Posts
    45
    I'd prefer to correct the problem myself if I can. I downloaded a DDF editor, but which values would I change to make certain fields variable length?

    Screenshot: http://www.probelaw.com/technology/ddfeditor.gif

  7. #7
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    You don't make certain fields variable length. The problem is that one of the fields is marked as variable length in terms of Btrieve but that field is broken down into multiple fields in terms of the DDFs. This is bad, very bad.
    You need to go back to the vendor and get updated DDFs (and possible application).
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  8. #8
    Join Date
    Jul 2004
    Posts
    45
    Have spoken to the software vendor and what do ya know...they don't support what I'm trying to do. They claim their DDFs are fine for what is supported, but this is not what I need.

    I really need a way of getting these particular numeric amounts. Isn't there anything I can do myself???

  9. #9
    Join Date
    May 2011
    Posts
    1

    My Issue

    I was having this problem today.

    Resolved it by changing the data type of a column from real to float.
    It seems i had some values that were invalid.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •