CREATE TEMPORARY TABLE

The CREATE TEMPORARY TABLE data description statement defines a temporary table. A temporary table exists for the duration of the transaction in which the table is created. When the transaction ends, CA IDMS deletes the definition of and the data associated with the temporary table.
idmscu19
The CREATE TEMPORARY TABLE data description statement defines a temporary table. A temporary table exists for the duration of the transaction in which the table is created. When the transaction ends, CA IDMS deletes the definition of and the data associated with the temporary table.
The CREATE TEMPORARY TABLE statement is a CA IDMS extension of the SQL standard.
This article describes the following information:
2
2
Authorization
None required.
Syntax
  ►►─── CREATE TEMPORARY TABLE 
table-identifier
 ────────────────────────────────►          ┌───────── , ─────────┐  ►─── ( ─▼─ 
column-definition
 ─┴ ) ───────────────────────────────────────────►◄  
Expansion of column-definition
  ►►─── 
column-name
  
data-type
 ─────────────────────────────────────────────────►  ►─┬────────────┬─────────────────────────────────────────────────────────────►    └─ NOT NULL ─┘  ►─┬────────────────┬─────────────────────────────────────────────────────────►◄    └─ WITH DEFAULT ─┘  
Parameters
  • table-identifier
    Specifies the name of the temporary table being created.
    Table-identifier
    must be a 1- through 18-character name that follows the conventions for SQL identifiers.
    Table-identifier
    must be unique within the transaction in which the temporary table is defined. To prevent possible ambiguity, temporary table identifiers should differ from the identifiers of any base tables and views defined in the dictionary.
  • column-definition
    Defines a column to be included in the temporary table.
    Columns are included in the table in the order they are specified.
    The list of column definitions must be enclosed in parentheses. Multiple column definitions must be separated by commas.
    Expanded syntax for
    column-definition
    is shown immediately following the CREATE TEMPORARY TABLE syntax. Descriptions for these parameters are located at the end of this section.
Parameters for Expansion of column-definition
  • column-name
    Specifies the name of a column to be included in the temporary table.
    Column-name
    must be a 1- through 32-character name that follows the conventions for SQL identifiers.
    Column-name
    must be unique within the temporary table being defined.
  • data-type
    Defines the data type for the named column. For expanded
    data-type
    syntax, see Expansion of Data-type.
  • NOT NULL
    Indicates that the column cannot contain null values.
    If you specify NOT NULL
    without
    WITH DEFAULT, an INSERT statement must specify a value for the column.
    If you do not specify NOT NULL, the column is defined to allow null values.
  • WITH DEFAULT
    Directs CA IDMS to store the default value for the named data type in the named column if no value for the column is specified when a new row is stored.
    The default value for a column is based on its data type:
Column data type
Default value
CHARACTER
Blanks
VARCHAR
A character string literal with a length of zero (that is, '')
GRAPHIC
Double-byte blanks
VARGRAPHIC
A double-byte character string literal with a length of zero
DATE
The value in the CURRENT DATE special register
TIME
The value in the CURRENT TIME special register
TIMESTAMP
The value in the CURRENT TIMESTAMP special register
All numeric data types
0 (zero)
Usage
Maximum Row Length
The total number of bytes allowed for all columns included in a temporary table is 32,767. The number of bytes used for each column is determined by the column data type. Columns that allow null values take one additional byte each.
Example
Defining a Temporary Table
The following CREATE TEMPORARY TABLE statement defines the temporary table TEMP_BUDGET with two columns:
create temporary table temp_budget    (dept_id      integer  not null,    all_expenses  decimal(9,2));