Number formats in SQL statements

[johnnyi80]

I am trying to update/insert records in a DB, using SQL statements. All numeric formats seem to work fine except for the Scientific (exponential) notations. In an UPDATE statement it raises "type mismatch" error (Example1). I managed to go arround it by using casting (Example2).

When using INSER statement however nothing worked. The scientific notation results in "type mismatch" (Example3). The attempt to cast the values was also not successfull (Example4).

Does anyone know how can I overcome the problem. I need to use scientific notation because of precision issues. There seems to be an inconsistency in the way various formats and castings are handled.

By the way I am using a local Paradox DB (Local SQL) and I do not wish to control the tables directly, but through SQL for later portability.

Thanks


Example1:
---------
UPDATE 'tblWireMaterialList'
SET MaterialSortOrder = 1,
MaterialRes = 1.345E-8 <--- "type mismatch" error

Example2:
---------
UPDATE 'tblWireMaterialList'
SET MaterialSortOrder = CAST ('1') AS INTEGER,
MaterialRes = CAST ('1.345E-8') AS NUMERIC <--- casting works in UPDATE


Example3:
---------
INSERT INTO 'tblMaterials'
(MaterialSortOrder, MaterialCode, MaterialRes)
VALUES (1, 'Fe', 1.345E-8) <--- the last value causes "tupe mismatch error

Example4:
---------
INSERT INTO 'tblMaterials'
(MaterialSortOrder, MaterialCode, MaterialRes)
VALUES (1, 'fe', CAST ('1.345E-8') AS NUMERIC) <--- does not work "illegal use of keyword"

 

[mickeyfeldman]

Unless I was actually performing calculations directly in the DB using stored procedures, or did not have control over the database structure, I would take the cowards way out and store this data as strings. In your application it would then be trival to convert the strings to numeric types when you needed to do the actual math. If precision or range of values really requires scientific notation, will you be able to retain that precision when using whatever numeric types your DB provides, in any case?

 

[johnnyi80]

Thanks a lot for the reply.

I used to use the trick you suggested - storing numbers as text, lot in the past, especially in cases when I was using Breieve as a record manager, but this time I would like to be able to communicate to the DB entirely through SQL including: returning calculated fields, Totals, INSERT/UPDATE/DELETE records etc.

I checked with MS-SQL server and there is no problem with scientific notation there. So I believe it is a problem with Borland's implementation of the Local SQL. The SQL parser does not seem to be working entirely according to the 92 specifications.

By the way I solved it in another way - by using parameters to pass the values - and it worked. However this is just a patch not a proper portable solution.

As regards range and precision of numbers at this development stage I am using local Paradox DB, so it is quite adequate and when I am ready I intend to port it to a proper SQL server.

Can you please confirm if the problem is Borland's Local SQL or is there something else I am overlooking.

Thanks.