The expanded parameters of data-type specify data types in an SQL data description statement.
Expansion of data-type
►►─┬─ BINary ─┬──────────────────────┬─────────────────────────┬──────────────►◄ │ └─ ( ─── length ─── ) ─┘ │ ├─ CHARacter ─┬──────────────────────┬──────────────────────┤ │ └─ ( ─── length ─── ) ─┘ │ ├─ DATE ────────────────────────────────────────────────────┤ ├─ DECimal ─┬───────────────────────────────────────────┬───┤ │ └─ ( ─── precision ───┬────────────────┬ ) ─┘ │ │ └─ , ─── scale ──┘ │ ├─ DOUBLE PRECISION ────────────────────────────────────────┤ ├─ FLOAT ─┬─────────────────────────┬───────────────────────┤ │ └─ ( ─── precision ─── ) ─┘ │ ├─ GRAPHIC ─┬──────────────────────┬────────────────────────┤ │ └─ ( ─── length ─── ) ─┘ │ ├─ INTeger ─────────────────────────────────────────────────┤ ├┬─ LONGINT ─┬──────────────────────────────────────────────┤ │└─ BIGINT ──┘ │ ├─ NUMeric ─┬───────────────────────────────────────────┬───┤ │ └ ( ─── precision ───┬────────────────┬─ ) ─┘ │ │ └─ , ─── scale ──┘ │ ├─ REAL ────────────────────────────────────────────────────┤ ├─ SMALLINT ────────────────────────────────────────────────┤ ├─ TIME ────────────────────────────────────────────────────┤ ├─ TIMESTAMP ───────────────────────────────────────────────┤ ├─ UNSIGNED DECimal─┬───────────────────────────────────┬───┤ │ └─ ( - precision ─┬────────────┬ ) ─┘ │ │ └ , - scale ─┘ │ ├─ UNSIGNED NUMeric ─┬──────────────────────────────────┬───┤ │ └ ( - precision ─┬────────────┬ ) ─┘ │ │ └ , - scale ─┘ │ ├┬─ VARCHAR ───────────┬─┬──────────────────────┬───────────┤ │└─ CHARacter VARYING ─┘ └─ ( ─── length ─── ) ─┘ │ └─ VARGRAPHIC ─┬──────────────────────┬─────────────────────┘ └─ ( ─── length ─── ) ─┘
Identifies a set of values that are fixed-length bit strings. BINARY values are represented by hexadecimal literals (for example, X'12A5E978').
Specifies the number of eight-bit bytes in a BINARY value. Length must be an integer in the range 1 through 32,760. The default is 1.
The maximum length of a column with a data type of BINARY is limited by page size and the total length of other columns in the table.
Note: For more information about the length of a BINARY value, see CREATE TABLE.
Identifies a set of values that are fixed-length single-byte character strings. CHARACTER values are represented by character string literals (for example, 'Past due').
Specifies the number of bytes in a CHARACTER value. Length must be an integer in the range 1 through 32,760. The default is 1.
The maximum length of a column with a data type of CHARACTER is limited by page size and the total length of other columns in the table.
Note: For more information about the length of a CHARACTER value, see CREATE TABLE.
Identifies the set of values that represent valid dates from January 1, 0001, through December 31, 9999. DATE values are represented by character string literals (for example, '1999-07-22').
The maximum length of a DATE value is 10 bytes. Internally, the length of a DATE value is always eight bytes.
Identifies a set of fixed-point, signed packed decimal values. DECIMAL values are represented by exact numeric literals (for example, 17.23).
The range of values included in the set is determined by the precision and scale specified for the data type. The largest possible DECIMAL value is 1031-1. The smallest possible DECIMAL value is -(1031-1).
Specifies the number of digits in a DECIMAL value. Precision must be an integer in the range 1 through 56. The default is 56.
The length of a DECIMAL value is equal to the precision plus 1, divided by 2.
Specifies the number of digits to the right of the decimal point in a DECIMAL value. Scale must be an integer in the range 0 through the precision of the DECIMAL value. The default is 0.
Identifies the set of 64-bit (long) floating-point values with a seven-bit exponent and a binary precision of 56. DOUBLE PRECISION values are represented by approximate numeric literals (for example, 0.1E-16).
The magnitude that can be represented by a positive DOUBLE PRECISION value ranges from approximately 5.4E-79 to approximately 7.2E+75. The magnitude that can be represented by a negative DOUBLE PRECISION value ranges from approximately -5.4E-79 to approximately -7.2E+75.
The length of a DOUBLE PRECISION value is eight bytes.
Identifies a set of floating-point values with a seven-bit exponent and a user-specified precision. FLOAT values are represented by approximate numeric literals (for example, -1.4E9).
The magnitude that can be represented by a positive FLOAT value ranges from approximately 5.4E-79 to approximately 7.2E+75. The magnitude that can be represented by a negative FLOAT value ranges from approximately -5.4E-79 to approximately -7.2E+75.
Specifies the binary precision of a FLOAT value. Precision must be an integer in the range 1 through 56. The default is 24.
If precision is less than or equal to 24, the length of a FLOAT value is four bytes. If precision is greater than 24, the length of a FLOAT value is eight bytes.
Identifies a set of values that are fixed-length double-byte character strings. GRAPHIC values are represented by double-byte character string literals (for example, G'<####>', where < and > represent the shift-out and shift-in characters and # represents a double-byte character).
The GRAPHIC data type is a CA IDMS extension of the SQL standard.
Specifies the number of characters in a GRAPHIC value. The length in bytes of a GRAPHIC value is equal to the number of bytes in one character times the number of characters.
Length must be an integer in the range 1 through 16,380. The default is 1.
The maximum length of a column with a data type of GRAPHIC is limited by page size and the total length of other columns in the table.
Note: For more information about the length of a GRAPHIC value, see CREATE TABLE.
Identifies the set of values that are 31-bit signed integers in the range -2,147,483,648 through 2,147,483,647. INTEGER values are represented by exact numeric literals (for example, -2874).
The length of an INTEGER value is four bytes.
Identifies the set of values that are 63-bit signed integers in the range -9,223,372,036,854,775,808 through 9,223,372,036,854,775,807. BIGINT values are represented by exact numeric literals (for example, 2187168).
The length of a BIGINT value is eight bytes. The keyword LONGINT can be used as a synonym for BIGINT but this is a CA IDMS extension of the SQL standard.
Identifies a set of fixed-point, signed zoned decimal values. NUMERIC values are represented by exact numeric literals (for example, -4.7). The use of NUM as a synonym for NUMERIC is a CA IDMS extension of the SQL standard.
The range of values included in the set is determined by the precision and scale specified for the data type. The largest possible NUMERIC value is 1031-1. The smallest possible NUMERIC value is -(1031-1).
Specifies the number of digits in a NUMERIC value. Precision must be an integer in the range 1 through 31. The default is 1.
The length in bytes of a NUMERIC value is equal to the precision.
Specifies the number of digits to the right of the decimal point in a NUMERIC value. Scale must be an integer in the range 0 through the precision of the NUMERIC value. The default is 0.
Identifies the set of 32-bit (short) floating-point values with a seven-bit exponent and a binary precision of 24. REAL values are represented by approximate numeric literals (for example, 0.4E52).
The magnitude that can be represented by a positive REAL value ranges from approximately 5.4E-79 to approximately 7.2E+75. The magnitude that can be represented by a negative REAL value ranges from approximately -5.4E-79 to approximately -7.2E+75.
The length of a REAL value is four bytes.
Identifies the set of values that are 15-bit signed integers in the range -32,768 through 32,767. SMALLINT values are represented by exact numeric literals (for example, 16433).
The length of a SMALLINT value is two bytes.
Identifies the set of values that represent valid times from 00.00.00 through 23.59.59.
TIME values are represented by character string literals (for example, '13.42.59').
The maximum length of a TIME value is eight bytes. Internally, the length of a TIME value is always eight bytes.
Note: A TIME value of 24.00.00 is accepted and treated as 00.00.00.
Identifies the set of values that represent valid date/time combinations with a precision of millionths of a second. Valid dates range from January 1, 0001, through December 31, 9999. Valid times range from 00.00.00.000000 through 23.59.59.999999.
TIMESTAMP values are represented by character string literals (for example, '1999-05-02-09.46.39.738294').
The maximum length of a TIMESTAMP value is 26 bytes. Internally, the length of a TIMESTAMP value is always eight bytes.
Identifies a set of fixed-point, unsigned packed decimal values. UNSIGNED DECIMAL values are represented by exact numeric literals (for example, 17.23).
The range of values included in the set is determined by the precision and scale specified for the data type. The largest possible UNSIGNED DECIMAL value is 1031-1. The smallest possible DECIMAL value is 0.
The UNSIGNED DECIMAL data type is a CA IDMS extension of the SQL standard.
Specifies the number of digits in an UNSIGNED DECIMAL value. Precision must be an integer in the range 1 through 31. The default is 1.
The length of an UNSIGNED DECIMAL value is equal to the precision plus 1, divided by 2.
Specifies the number of digits to the right of the decimal point in an UNSIGNED DECIMAL value. Scale must be an integer in the range 0 through the precision of the UNSIGNED DECIMAL value. The default is 0.
Identifies a set of fixed-point, unsigned zoned decimal values. UNSIGNED NUMERIC values are represented by exact numeric literals (for example, 4.7).
The range of values included in the set is determined by the precision and scale specified for the data type. The largest possible UNSIGNED NUMERIC value is 1031-1. The smallest possible NUMERIC value is 0.
The UNSIGNED NUMERIC data type is a CA IDMS extension of the SQL standard.
Specifies the number of digits in an UNSIGNED NUMERIC value. Precision must be an integer in the range 1 through 31. The default is 1.
The length in bytes of an UNSIGNED NUMERIC value is equal to the precision.
Specifies the number of digits to the right of the decimal point in an UNSIGNED NUMERIC value. Scale must be an integer in the range 0 through the precision of the NUMERIC value. The default is 0.
Identifies a set of values that are variable-length single-byte character strings. VARCHAR values are represented by character string literals (for example, 'Customer address needs to be verified').
Specifies the maximum number of characters in a VARCHAR value. Length must be an integer in the range 1 through 32,758. The default is 1.
The length of a VARCHAR value is the number of characters in the value. The number of bytes reserved for a VARCHAR value is always the same; the maximum length, plus 2 regardless of the length of the VARCHAR value. A VARCHAR value is preceded by a 2-byte binary length of the value.
The maximum length of a column with a data type of VARCHAR is limited by page size, the total length of other columns in the table, and other factors.
Note: For more information about the length of a VARCHAR value, see CREATE TABLE.
Identifies a set of values that are variable-length double-byte character strings. VARGRAPHIC values are represented by double-byte character string literals (for example, G'<####>', where < and > represent the shift-out and shift-in characters and # represents a double-byte character).
The VARGRAPHIC data type is a CA IDMS extension of the SQL standard.
Specifies the maximum number of characters in a VARGRAPHIC value. Length must be an integer in the range 1 through 16,379. The default is 1.
The length of a VARGRAPHIC value is the number of characters in the value. The numbeCREATE TABLEr of bytes reserved for a VARGRAPHIC value is the maximum length times the number of bytes for one character, plus 2. A VARGRAPHIC value is preceded by a 2-byte binary length of the value.
The maximum length of a column with a data type of VARGRAPHIC is limited by page size and the total length of other columns in the table.
Note: For more information about the length of a VARGRAPHIC value, see CREATE TABLE.
Graphics Data
The use of graphics data requires the installation of CA IDMS DBCS.
Defining Table Columns
The following CREATE TABLE statement creates a table with ten columns. Each column is associated with a data type. The data type specification determines the set of values that can occur in the column.
create table job (job_id integer not null, job_title character(20) not null, job_desc_line_1 varchar(60), job_desc_line_2 varchar(60), min_rate decimal(8,2), max_rate decimal(8,2), salary_ind character(1), num_of_positions smallint, num_open smallint, eff_date date);
|
Copyright © 2014 CA.
All rights reserved.
|
|