 |
» |
|
|
|
All the data in a column must be of the same type.
Specify the data type for each column when you create a
table or when you add a column to an existing table.
The ALLBASE/SQL data types and the values you can specify
for data of each type are shown in Table 7-1 “ALLBASE/SQL Data Types”.
Table 7-1 ALLBASE/SQL Data Types | Group | Data Type | Description |
|---|
| Alphanumeric | |
String of fixed length n, where n
is an integer from 1 to 3996 bytes. The default size is CHAR (1).
The keyword CHARACTER is a synonym for CHAR. | | | VARCHAR(n) |
String of variable length no greater than n,
where n must be an integer from 1 to 3996 bytes.
| | Numeric | |
Fixed-point packed decimal number with a precision
(maximum number of digits excluding sign and decimal point)
no greater than p, where
p is 1 through 27, and a scale (number of digits to the right of
the decimal) of s, where s is from 0 through p.
E (exponential) and L (Pascal longreal) notation are not allowed in the
specification of a decimal value. Operations on data of type
DECIMAL are often much more precise than operations on data of type FLOAT.
| The default for NUMERIC and DECIMAL types is DECIMAL (27,0).
DEC and NUMERIC are synonyms for DECIMAL. | | | or
DOUBLE PRECISION |
Long (64-bit) floating point number. This is
an approximate numeric value consisting of an exponent and a mantissa.
The precision, p, is a positive integer that specifies
the number of significant binary digits in the mantissa.
The value of p can be from 25 to 53. The default is 53.
| The range of negative numbers that
can be represented is -1.79769313486230E+308
to -2.22507385850721E-308.
The range of positive numbers that can be represented is
2.22507385850721E-308 to 1.79769313486230E+308.
E (exponential) or L (Pascal longreal)
notation can be used to specify FLOAT values.
| DOUBLE PRECISION is a synonym for FLOAT(53). | | | FLOAT(p) or REAL |
Short (32-bit) floating point number. This is an approximate numeric value
consisting of an exponent and a mantissa.
The precision, p, is a positive integer that specifies the number
of significant binary digits in the mantissa. The value of p can be
from 1 to 24. The default (using REAL) is 24.
The range of negative numbers that can be represented is
-3.402823E+38 to -1.175495E-38.
The range of positive numbers that can be represented is
3.402823E+38 to 1.175495E-38.
| REAL is a synonym for FLOAT (24).
| | | | | Integer in the range -2147483648
(-231) to 2147483647 (231-1).
INT is a synonym for INTEGER. | | | | SMALLINT | Integer in the range
-32768 (-215) to 32767 (215-1).
| | | | | Date/Time | DATE |
String of form 'YYYY-MM-DD', where YYYY represents the calendar
year, MM is the month, and DD is the day of the month.
DATE is in the range from '0000-01-01' to '9999-12-31'. | | | | | | TIME | String of the form 'HH:MI:SS: where HH represents
hours, MI is minutes, and SS is seconds. TIME is in the range from
'00:00:00' to '23:59:59'. | | | | | | DATETIME | String of the form 'YYYY-MM-DD HH:MI:SS.FFF',
where YYYY represents the
calendar year, MM is the month, DD is the day, HH the hour,
MI the minute, SS the second, and FFF thousandths of a second.
The range is from '000-01-01 00:00:00.000' to '9999-12-31
23:59:59.999'.
| | | | | | INTERVAL | String of the form 'DDDDDDD HH:MI:SS.FFF', where
DDDDDDD is a number of days, HH a number of hours, MI
a number of minutes, SS a number of seconds, and FFF a number
of thousandths of a second.
The range is from '0 00:00:00.000' to
'3652436 23:59:59.999'.
| | | | | Binary | BINARY(n) | Binary string of fixed length n, where n is
an integer from 1 to 3996 bytes. Each byte stores 2
hexadecimal digits. | | | | | | VARBINARY(n) | Binary string of variable length
no greater than n, where n is
an integer from 1 to 3996 bytes. Each byte stores 2
hexadecimal digits. | | | | | | LONG BINARY(n) | Binary string of fixed length n, where
n is an integer from 1 to (231-1) bytes. | | | | | | LONG VARBINARY(n) | Binary string of variable length
no greater than n, where n is
an integer from 1 to (231-1) bytes.
| | | |
Your choice of data types can affect the following: How values are used in expressions. Some operations
can be performed only with data of a certain type. For
example, arithmetic operations are limited to numeric and date/time
data types, such as INTEGER, SMALLINT, FLOAT, DECIMAL,
DATE, TIME, DATETIME, or INTERVAL.
Pattern matching with the LIKE predicate can be
performed only with string data, that is, data of types
CHAR or VARCHAR.
The result of operations combining data of different
types. When comparisons and expressions combining
data of different but compatible types are
evaluated, ALLBASE/SQL performs type conversion, as
described later in this chapter.
How values are transferred programmatically. When data
is transferred between ALLBASE/SQL and an application
program in host variables, ALLBASE/SQL uses the data
type equivalencies described in the ALLBASE/SQL application
programming guides.
The following table contains the storage requirements of the various data types. Table 7-2 Data Type Storage Requirements |
Type
|
Storage Required |
|---|
|
CHAR (n)
|
n bytes (where n must be an integer from 1 to 3996)
| |
VARCHAR (n)
|
n bytes (where n must be an integer from
1 to 3996)
| |
DECIMAL (p[,s])
|
4 bytes (where p <= 7) or 8 bytes (where 7 < p <= 15) or 12 bytes
(where 15 < p <= 23) or 16 bytes (where p > 23)
| |
FLOAT
|
8 bytes
| |
REAL
|
4 bytes
| |
INTEGER
|
4 bytes. Integer values less than -2147483648 (-2**31) or larger than
2147483647 (2**31 - 1) up to 15 digits long are stored as decimals with
a precision of 15 and a scale of 0, i.e., equivalent to DECIMAL (15,0)
| |
SMALLINT
|
2 bytes
| |
DATE
|
16 bytes
| |
TIME
|
16 bytes
| |
DATETIME
|
16 bytes
| |
INTERVAL
|
16 bytes
| |
BINARY (n)
|
n bytes (where n must be an integer from 1 to
3996)
| |
VARBINARY (n)
|
n bytes (where n must be an integer from 1 to
3996)
| |
LONG BINARY (n)
|
n bytes (where n must be an integer from 1 to
231 - 1) | |
LONG VARBINARY (n)
|
n bytes (where n must be an integer from 1 to
231 - 1)
|
|
