[EMAIL PROTECTED] writes:
> 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.
Hi Ben,
You get an integer overflow in num*2<2147483647. Try this instead to use
bigint arithmetic:
UPDATE t1 SET num=CASE
WHEN CAST(num AS BIGINT)*2<2147483647
THEN num*2
ELSE
2147483647
END
WHERE id>=0 AND id<=10;
Hope this helps.
--
Knut Anders
