HP 3000 Manuals HP 3000 Manuals
Type Specifications
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 .
Table 7-1. ALLBASE/SQL Data Types
-------------------------------------------------------------------------------------------------------
| | | |
| Group | Data Type | Description |
| | | |
-------------------------------------------------------------------------------------------------------
| | | |
| Alphanumeric | CHAR[ACTER][(n)] | 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. |
| | | |
-------------------------------------------------------------------------------------------------------
| [REV BEG] | | |
| | | |
| Numeric | DEC[IMAL][(p[,s])] | Fixed-point packed decimal number with a precision |
| | NUMERIC[(p[,s])] | (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.[REV END] |
| | | |
| | FLOAT[(p)] or DOUBLE | Long (64-bit) floating point number. This is an |
| | PRECISION | 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). |
| | | |
| | INT[EGER] | 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). |
| | | |
-------------------------------------------------------------------------------------------------------
Table 7-1. ALLBASE/SQL Data Types (cont.)
----------------------------------------------------------------------------------------------
| | | |
| Group | Data Type | Description |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| 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) |
| | |
-----------------------------------------------------------------------------------------------------
| [REV BEG] | |
| | |
| 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)[REV |
| | END] |
| | |
-----------------------------------------------------------------------------------------------------
| | |
| 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) |
| | |
-----------------------------------------------------------------------------------------------------