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.

The CREATE TEMPORARY TABLE statement is a CA IDMS extension of the SQL standard.

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));