db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <Knut.Hat...@Sun.COM>
Subject Re: INTEGER size limit?
Date Tue, 21 Oct 2008 07:45:15 GMT
BenCollver@gmail.com 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

Mime
View raw message