Numeric cast local sql
Damien Collings
error "type mismatch in expression" :-
insert into "DestTable.dbf" (Field1,Field2)
select Src.Field1,cast(13 as numeric(4,0))
from "SrcTable.dbf" Src
NOTE
---------
* Field1 is Numeric(6,0)
* Field2 is Numeric(4,0)
* Src.Field1 is Numeric(6,0)
* Currently using BDE 5.01 (latest 5.02 update patch installed)
I believe the problem only arises because the cast function is failing to
cast a literal value to a numeric field size smaller than (6,0). If I
replace the cast statement with Src.Field2 where Src.Field2 is also a
numeric(4,0) the statement works. Any ideas? This appears to be
bug/limitation of local SQL. I am out of ideas on this one...
Damien Collings
Steve Koterski
<d_col...@dse.com.au> wrote:
>Can anyone offer advice on the following local SQL sample, it returns an
>error "type mismatch in expression" :-
>
>insert into "DestTable.dbf" (Field1,Field2)
>select Src.Field1,cast(13 as numeric(4,0))
>from "SrcTable.dbf" Src
What is the table type you are using? dBASE, Paradox, or FoxPro? What table
level? Why are you using the data types NUMERIC(6, 0) and NUMERIC(4, 0),
when the data type SMALLINT would be better for this data? (Actually, use
of these data types suggests to me that you are using dBASE tables.) Under
what program were the tables created?
I cannot reproduce the error you are getting. I tested under BDE 5.01
(working in SQL Explorer).
Test Paradox tables created with the statements:
CREATE TABLE "DamienCollings1.db"
(
Field1 NUMERIC(6, 0),
Field2 NUMERIC(4, 0)
)
CREATE TABLE "DamienCollings2.db"
(
Field1 NUMERIC(6, 0)
)
Data inserted into the source table with the statement:
INSERT INTO "DamienCollings2.db"
(Field1)
VALUES (12.0)
INSERT INTO "DamienCollings2.db"
(Field1)
VALUES (99.0)
INSERT INTO "DamienCollings2.db"
(Field1)
VALUES (1024.0)
And data copied (successfully and without error) from source table to
target table with the INSERT statement:
INSERT INTO "DamienCollings1.db"
(Field1, Field2)
SELECT Field1, CAST(3.0 AS NUMERIC(4, 0))
FROM "DamienCollings2.db"
This variation also worked (using the literal 3 instead of 3.0):
INSERT INTO "DamienCollings1.db"
(Field1, Field2)
SELECT Field1, CAST(3 AS NUMERIC(4, 0))
FROM "DamienCollings2.db"
There was a bug in an earlier 32-bit version of the BDE, circa 3 or 4, that
involved numeric literals in a SELECT subquery used as the source for an
INSERT statement. But I have not seen reports of this for quite a while.
//////////////////////////////////////////////////////////////////////////
Steve Koterski "My problem lies in reconciling my gross
Technical Publications habits with my net income."
INPRISE Corporation -- Errol Flynn (1909-1959)
http://www.borland.com/delphi
Damien Collings
Thank you for your response to my numeric cast problem, I have been trying
to deal with this for some time now. I feel from your response that I have
not given you enough information to re-produce the problem I have.
1) I'm using FoxPro 2.6 tables, but this problem arises whether I use DBase
or FoxPro tables.
2) The reason I need to use this field structure as apposed to a SMALLINT is
because I am reading data from an AS400 box and need to exactly match the
data that has been defined on that machine.
3) If I take the literal cast out and replace it with another field from the
second table which is the same size (or smaller) in structure, the Insert
statement works. This is implies that local SQL is at least checking the
field definitions correctly when they are referenced by "field names" in
SQL. The problem is purely on the literal cast to a numeric smaller than
(6,0).
PLEASE NOTE
Your sample code fails when using FoxPro or DBase tables. The two sql
"create table" statements you gave both produce Paradox tables. Your sample
code works fine on Paradox tables. However, for you to re-create the bug I'm
referring to, try the SQL snippet below on the FoxPro tables that I have
provided. This was created in Visual FoxPro and shows the correct field
sizes when viewed in something like Database Desktop. (It also fails if I
set the BDE default to be FOXPRO and use your local SQL samples to create
the tables).
Change your "insert" sample code to reference ".dbf" instead of ".db" :-
INSERT INTO "DamienCollings1.dbf" (Field1, Field2)
SELECT Field1, CAST(3.0 AS NUMERIC(4, 0))
FROM "DamienCollings2.dbf"
you will find that you get "mismatch in expression". It appears that local
SQL is incapable of casting a literal value to a numeric structure smaller
than (6,0).
Try this and let me know what you think.
Damien.
P.S Using the "values" clause in an insert does work on FoxPro tables, it's
only when trying to insert as above that this problem is generated.
Steve Koterski
<d_col...@dse.com.au> wrote:
>Thank you for your response to my numeric cast problem, I have been trying
>to deal with this for some time now. I feel from your response that I have
>not given you enough information to re-produce the problem I have.
>
>1) I'm using FoxPro 2.6 tables, but this problem arises whether I use DBase
>or FoxPro tables.
>2) The reason I need to use this field structure as apposed to a SMALLINT is
>because I am reading data from an AS400 box and need to exactly match the
>data that has been defined on that machine.
>3) If I take the literal cast out and replace it with another field from the
>second table which is the same size (or smaller) in structure, the Insert
>statement works. This is implies that local SQL is at least checking the
>field definitions correctly when they are referenced by "field names" in
>SQL. The problem is purely on the literal cast to a numeric smaller than
>(6,0).
[...]
I tried your scenario using dBASE tables (level 7). The insertion worked
without error. So I think we need to isolate what is different in your
case.
1. What version of the BDE are you using? I seem to remember this sort of
situation as a bug in one or more earlier versions of the BDE, but that
it no longer manifests itself in more recent versions. "This sort of
situation" meaning using a SELECT subquery as the source for an INSERT
statement where that subquery has a literal in its SELECT clause. For
the BDE version number, look at the timestamp of the main BDE DLL file,
IDAPI32.DLL: 4:00 meaning version 4.0, 5:01 for version 5.01, and so on.
2. What level of dBASE tables did you use (where the process failed)? Use
the Database Explorer (SQL Explorer if you have Delphi Client/Server) to
inspect the properties of the tables.
3. What language driver was used to create the dBASE tables? Use the BDE
Administrator to get this information. On the Configuration tab navigate
to the node:
Configuration
Drivers
Native
dBASE
4. What language driver was used to create the FoxPro tables?
5. You were using the native drivers to access both the dBASE and FoxPro
tables, right? Not ODBC or a third-party database engine?
6. What is the ENABLE BCD property for the alias set to? (Mine is set to
FALSE.)
7. Under what software were the FoxPro tables created? Borland? Other?
At any rate, local SQL itself does support the type of data conversion
using local SQL that you describe. It is just that you may be experiencing
problems with the implementation of that support due to short-comings in an
older version of the BDE, due to short-comings in a particular language
driver (could be old or new), or some external influence (like a bad or
incorrect file format, who knows?).