>From http://db.apache.org/derby/docs/10.4/ref/rrefnumericlimits.htm
The largest INTEGER is 2,147,483,647.


ij>describe t1;
COLUMN_NAME |TYPE_NAME|DEC&|NUM&|COLUM&|COLUMN_DEF|CHAR_OCTE&|IS_NULL&
------------------------------------------------------------------------------
ID |INTEGER |0 |10 |10 |NULL |NULL |YES
NUM |INTEGER |0 |10 |10 |NULL |NULL |YES
NAME |VARCHAR |NULL|NULL|128 |NULL |256 |YES

3 rows selected


ij> UPDATE t1 SET num=CASE WHEN num*2<2147483647 THEN num*2 ELSE 2147483647 END WHERE id>=0 AND id<=10;
ERROR 22003: The resulting value is outside the range for the data type INTEGER.

ij> UPDATE t1 SET num=CAST(num*2 AS INTEGER) WHERE id>=0 AND id<=10;
ERROR 22003: The resulting value is outside the range for the data type INTEGER.

ij> UPDATE t1 SET num=CAST(CAST(num AS BIGINT)*2 AS INTEGER) WHERE id>=0 AND id<=10;
ERROR 22003: The resulting value is outside the range for the data type INTEGER.


I expected these queries to assign a value within the range of an INTEGER. Where did I go wrong?

Thank you,

Ben