Importing Constants and Special Constants

For column descriptions, you can use the syntax rule column_assignment to specify that a constant or a special constant is imported into the specified column for each imported data record, instead of the value from the data stream.

Syntax

<column_assignment> ::= <column_name> '<literal>'
                      | <column_name> <column_function>

<column_name>       ::= <identifier>

<column_function>   ::= DATE | FALSE | STAMP | TIME | TIMESTAMP
                      | TRUE | USER | USERGROUP | <sequence_number>

<sequence_number>   ::= SEQNO
                      | SEQNO <integer>
                      | SEQNO <integer> <integer>

<column_name>

Name of column

See the SQL Reference Manual, Column Name (column_name)

<literal>

Literal

See the SQL Reference Manual, Literal (literal)

DATE

Current date

The column into which you want to import one of these values must have the type DATE.

FALSE

Boolean value FALSE

STAMP

A unique value generated by the database system that can only be imported into columns of the type CHAR (n) BYTE where n >= 8.

TIME

Current time

The column into which you want to import one of these values must have the type TIME.

TIMESTAMP

Current timestamp

The column into which you want to import one of these values must have the type TIMESTAMP.

TRUE

Boolean value TRUE

USER

The name of the current user is imported.

USERGROUP

Name of the group of the current user is imported.

If this user is not assigned to a user group, the name of the current user is imported.

The column into which you want to import one of these values must have the type CHAR (n) where n >= 32.

SEQNO

Sequence value

The start value is 0 and the subsequent numbers increase by 1 each time

SEQNO <integer>

Sequence value

The start value (the first value to be imported) is the integer integer. The subsequent numbers increase by 1 each time

SEQNO <integer> <integer>

Sequence value

The start value integer (the first value to be imported) is specified directly after the keyword SEQNO. The next integer specifies the interval between the values to be imported.

See the SQL Reference Manual, CREATE SEQUENCE Statement (create_sequence_statement)

Examples

IMPORT TABLE hotel.reservation
DATA INSTREAM 'reservation_csv.data'
  rno        1
  cno        2
  hno        3
  type       4
  arrival    '20040207'
  departure  '20040220'

Constants 20040207 and 20040220 (date in format INTERNAL) are imported.

IMPORT TABLE hotel.reservation
DATA INSTREAM 'reservation_csv.data'
  rno        1
  cno        2
  hno        3
  type       4
  arrival    DATE
  departure  5

The special constant DATE is imported.

IMPORT TABLE hotel.customer
DATA INSTREAM 'customer_csv.data'
  cno       
SEQNO 3000 100
  title     2
  firstname 3
  name      4
  zip       5
  address   6

The specifications for the sequence (start position 3000, interval 100) are imported.

Explanation

In the command, enter the constant literalor the special constant column_function instead of a position specification. Now, data will not be imported from the data stream for the appropriate column.

If the data stream is empty, the constants specified in the command are not imported.

<column_name> '<literal>'

To import a general constant literal, enter the required constant instead of a position. Place the value in single quotation marks.

The Loader treats the constant like a plain text value and converts it into the data type of the target column.

If you want to import the constant into a numeric column, it must have a valid numeric format.

<column_name> <column_function>

You can import special column_function constants. The following values are possible: DATE, FALSE, STAMP, TIME, TIMESTAMP, TRUE, USER, USERGROUP and sequence_number.

<sequence_number>

You can use the syntax rule sequence_number to import automatically generated number generators with a freely definable start value and increment.

The set of calculable numbers is cyclical. This means that the value that comes after the largest possible value is the smallest possible value. In this way, you can generate as many numbers as you want, including repeated sequential numbers.

If you use the sequence_number syntax rule, sequential numbers should be generated according to one of the three options. Both the start value and the increment can be negative. A column of the type FIXED(10) is enough for storing the sequential numbers.

See also:

IMPORT COLUMNS Command

IMPORT TABLE Command

UPDATE COLUMNS Command