[All]
arithmetic exception, numeric overflow, or string truncation.
Abstract: arithmetic exception, numeric overflow, or string truncation.
Problem:
Inserting/updating record A (field length (10) with a value from
record B (field length (60)) results in a string truncation error.
Solution:
Metadata:
CREATE DATABASE test.gdb
CREATE TABLE T1 (
c1 CHAR(5),
c2 CHAR(2));
Example: (incorrect usage)
INSERT INTO t1(c1) VALUES ("a");
INSERT INTO t1(c1) VALUES ("ab");
INSERT INTO t1(c1) VALUES ("abc");
SELECT * FROM t1;
C1 C2
====== ======
a
ab
abc
The following statements fail with the error:
"arithmetic exception, numeric overflow, or string truncation"
as they should. Otherwise you would have data loss with the last record "abc".
INSERT INTO t1(c2) SELECT c1 FROM t1;
UPDATE t1 SET c2 = c1;
UPDATE t1 SET c2 = CAST(c1 AS CHAR(2));
Example: (correct usage)
You can insert values into a smaller column from
a larger column as long as the value being inserted
will fit into the range of the destination column.
You can do this with the CAST() function.
SELECT * FROM t1;
C1 C2
====== ======
a
ab
The following statement succeeds as it should.
UPDATE t1 SET c2 = CAST(c1 AS CHAR(2));
SELECT * FROM t1;
C1 C2
====== ======
a a
ab ab
