Board index » delphi » INSERT SQL query question (Integer Types)

INSERT SQL query question (Integer Types)

RE:  Field type conversion problem

When I run this query

INSERT INTO "Table1.db" (CellColor)
SELECT DISTINCT 255 AS CellColor
FROM "Table2.db"

I get a type mismatch.  The CellColor in both tables are of type
"I" or integer.  I have norrowed the problem down to SQL
conversion.  It converts the integer type into a "N" or number.
Is there a way to specify the field type when doing the value
calculation.

Thanks,

Joel Hynes
MarTech Systems,Inc.

 

Re:INSERT SQL query question (Integer Types)


Quote
On Wed, 23 Sep 1998 09:00:25 -0500, Joel Hynes <jo...@dwave.net> wrote:
>When I run this query

>INSERT INTO "Table1.db" (CellColor)
>SELECT DISTINCT 255 AS CellColor
>FROM "Table2.db"

>I get a type mismatch.  The CellColor in both tables are of type
>"I" or integer.  I have norrowed the problem down to SQL
>conversion.  It converts the integer type into a "N" or number.
>Is there a way to specify the field type when doing the value
>calculation.

One of the quirks of Paradox tables.

If the Table1.CellColor column is of type INTEGER, use the SQL function
CAST to convert the literal 255 to INTEGER or SMALLINT.

  INSERT INTO "Table1.db" (CellColor)
  SELECT DISTINCT CAST(255 AS INTEGER) AS CellColor
  FROM "Table2.db"

//////////////////////////////////////////////////////////////////////////
Steve Koterski                "I don't work for any company named Inpoop."
Technical Publications                           -- Steve Koterski, 1957-?
INPRISE Corporation
http://www.inprise.com/delphi

Re:INSERT SQL query question (Integer Types)


Hi there,

I had the same problem in a code block like bellow:

  select price from table1
  union all
  select 1 as price from table2

I solved the problem by changing:

  select (cast price as float) as price  from table1
  union all
  select 1 as price from table2

Hope this will be useful for you.
Cezar Nechifor

Quote
Joel Hynes wrote:
> RE:  Field type conversion problem

> When I run this query

> INSERT INTO "Table1.db" (CellColor)
> SELECT DISTINCT 255 AS CellColor
> FROM "Table2.db"

> I get a type mismatch.  The CellColor in both tables are of type
> "I" or integer.  I have norrowed the problem down to SQL
> conversion.  It converts the integer type into a "N" or number.
> Is there a way to specify the field type when doing the value
> calculation.

> Thanks,

> Joel Hynes
> MarTech Systems,Inc.

Other Threads