Progress
SQL-92
Guide and Reference


Exact Numeric Data Types

See the "Numeric Literals" section for information on specifying values to be stored in numeric columns.

This is the syntax for an exact_numeric_data_type:

SYNTAX
TINYINT  |  SMALLINT  |  INTEGER    
  | NUMERIC | NUMBER [ ( precision [ , scale ] ) ] 
  | DECIMAL [ ( precision , scale ) ]  

TINYINT

Corresponds to an integer value in the range -128 to +127 inclusive.

SMALLINT

Corresponds to an integer value in the range of -32768 to +32767 inclusive.

INTEGER

Corresponds to an integer value in the range of -2 ** 31 to 2 ** 31-1 inclusive.

NUMERIC | NUMBER [ ( precision [ , scale ] ) ]

Corresponds to a number with the given precision (maximum number of digits) and scale (the number of digits to the right of the decimal point). By default, NUMERIC columns have a precision of 32 and a scale of 0. If NUMERIC columns omit the scale, the default scale is 0.

The range of values for a NUMERIC type column is -n to +n where n is the largest number that can be represented with the specified precision and scale. If a value exceeds the precision of a NUMERIC column, SQL generates an overflow error. If a value exceeds the scale of a NUMERIC column, SQL rounds the value.

NUMERIC type columns cannot specify a negative scale or specify a scale larger than the precision.

EXAMPLE

The following example shows what values can be inserted into a column created with a precision of 3 and scale of 2:

-- Illustrate bounds for precision 3 and scale 2
create table bounds (col1   numeric(3,2));
Update count = 0.
 
insert into bounds values(33.33);
 
ErrorCode=-20052
Error in executeDirect:Overflow error;Rolledback Xn
 
 
insert into bounds values(33.9);
 
ErrorCode=-20052
Error in executeDirect:Overflow error;Rolledback Xn
 
 
insert into bounds values(3.3);
Update count = 1.
 
insert into bounds values(33);
 
ErrorCode=-20052
Error in executeDirect:Overflow error;Rolledback Xn
 
 
insert into bounds values(3.33);
Update count = 1.
 
insert into bounds values(3.3333);
Update count = 1.
 
insert into bounds values(3.3555);
Update count = 1.
 
select * from bounds;
 
            COL1 
---------------- 
            3.33 
            3.36 
             3.3 
            3.33 

DECIMAL [ ( precision , scale ) ]

Equivalent to type NUMERIC.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095