[probelaw]

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??

[mirtheil]

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?

[probelaw]

"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.

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

[probelaw]

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.

[mirtheil]

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.

[probelaw]

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

[mirtheil]

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).

[probelaw]

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???