using INSERT

Konnor · #1 (**permalink**) 03-09-06, 04:49

I have just started with DB2 and have set up a small database called 'TEST' with the following table.

Code:

CREATE TABLE Customer
(
	CustomerID	INTEGER NOT NULL,
	Name		VARCHAR(20) NOT NULL
)

when I try to insert any data with something like

Code:

INSERT INTO Customer (CustomerID, Name) VALUES ('12', 'Trev');

It throws up the error

Code:

 A value is not compatible with the data type of its assignment 
target.  Target name is "CUSTOMERID".  SQLSTATE=42821

SQL0408N  A value is not compatible with the data type of its assignment target.  Target name is "CUSTOMERID

I'm fairly sure '12' is a non null integer.

Many thanks,

sathyaram_s · #2 (**permalink**) 03-09-06, 06:12

'12' is a string ... 12 is an integer ... Unlike some other databases, DB2 does not do an implicit type conversion between dissimilar data types .. ie , An integer value of say 12 can go into a smallint field or a bigint field, but not into a char field ... A char(5) column's value can go into a varchar(20) column but not into an integer column even if the contents are just numbers ..

INSERT INTO Customer (CustomerID, Name) VALUES (12, 'Trev');

Sathyaram

Internet Services

Web Development

Digital Marketing

Consumer Tech