SQL Reference Guide > SQL Reference Guide > Understanding the Elements of SQL Statements > SQL Operations > Assignment Operations
Was this helpful?
Assignment Operations
An assignment operation places a value in a column or variable. Assignment operations occur during the execution of INSERT, UPDATE, FETCH, CREATE TABLE...AS SELECT, and embedded SELECT statements. Assignments can also occur within a database procedure.
When an assignment operation occurs, the data types of the assigned value and the receiving column or variable must either be the same or comparable.
• If the data types are not the same, comparable data types are converted.
• If the data types are not comparable, convert the assignment value into a type that is the same or comparable with the receiving column or variable.
For information about the type conversion functions, see Default Type Conversion.
Character String Assignments
All character types are comparable with one another and with integer, decimal, and float types. Any character string can be assigned to any column or variable of character data type. A character string can also be assigned to a column or variable of integer, decimal, or float type, as long as the content of the string is a valid numeric value. The result of the assignment depends on the types of the assignment string and the receiving column or variable.
Assigned String
Receiving Column or Variable
Description
Fixed-length (c or char)
Fixed-length
Note:  The assigned string is truncated or padded with spaces if the receiving column or variable is not the same length as the fixed length string. If the assigned string is truncated to fit into a host variable, a warning condition is indicated in SQLWARN. For a discussion of the SQLWARN indicators, see SQL Communications Area (SQLCA).
Fixed-length
Variable-length (varchar, long varchar, or text)
The trailing spaces are trimmed. If the receiving column or variable is shorter than the fixed length string, the fixed length string is truncated from the right side. If the assignment was to a variable, a warning condition is indicated in SQLWARN. For a discussion of the SQLWARN indicators, see SQL Communications Area (SQLCA).
Note:  If a long varchar value over is assigned to another character data type, the result is truncated at the maximum row size configured but not exceeding 32,000 (16,000 in a UTF8 instance).
Variable-length (varchar, long varchar, or text)
Fixed-length
The assigned string is truncated or padded with spaces if the receiving column or variable is not the same length as the variable length string.
Note:  If a long varchar value over is assigned to another character data type, the result is truncated at the maximum row size configured but not exceeding 32,000 (16,000 in a UTF8 instance).
Variable-length
Variable-length
The variable length string is truncated if the receiving column or variable is not long enough.
String Truncation
If an attempt is made to insert a string value into a table column that is too short to contain the value, the string is truncated.
String truncation can occur as a result of the following statements:
• COPY
• CREATE TABLE...SELECT
• INSERT
• UPDATE
Specify Error Handling for String Truncation
To change error handling for string truncation, use any of these methods:
• Specify the -STRING_TRUNCATION=option flag on the CONNECT statement, specified when a session connects to a database.
• Specify SET STRING_TRUNCATION=option flag for the session.
• Specify the config.string_truncation configuration parameter in config.dat using CBF or the iisetres command.
This flag can also be specified on the command line for Ingres operating system commands that accept SQL option flags.
The option can be IGNORE, WARN, or FAIL. For details, see STRING_TRUNCATION.
Numeric Assignments
All numeric types are compatible with one another and with character types. Money is compatible with all of the numeric and string data types.
Numeric assignments follow these rules:
• The DBMS Server can truncate leading zeros, or all or part of the fractional part of a number if necessary. If truncation of the non-fractional part of a value (other than leading zeros) is necessary, an overflow error results. These errors are reported only if numeric overflow error handling (see Specify Error Handling for Arithmetic) is set to warn or fail.
• If the receiving column or variable specifies more digits to the right of the decimal point than is present in the assignment value, the assignment value is padded with trailing zeros.
• When a float, float4, decimal, or money value is assigned to an integer column or variable, the fractional part is truncated.
• When a decimal value with a scale greater than two is assigned to a money column or variable, the fractional value is rounded.
• Character data is subject to numeric syntax checks if assigned in a numeric or money context.
ANSI Date and Time Assignments
The ANSI date and time data types are compatible between themselves and string columns, and follow the rules of coercion described in Coercion Between Datetime Data Types.
The values of all date and time types can be assigned to string columns. The result is a display version of each value. String values can also be assigned to date and time columns, as long as the string values correspond to the acceptable input format for the particular date and time type.
Note:  When a date and time value is retrieved from a database, it is always presented to the user in the appropriate display format in a string variable.
Ingresdate Assignments
The ingresdate data type is compatible with string data types if the value in the string is a valid representation of a date or time input format.
Absolute date or interval column values can be assigned to an ingresdate column. In addition, a string literal, a character string host variable, or a character string column value can be assigned to an ingresdate column if its value conforms to the valid input formats for Ingres dates or times.
When a date value is assigned to a character string, the DBMS Server converts the date to the display format (see How Ingres Dates and Times Are Displayed).
Logical Key Assignments
There are two types of logical keys:
TABLE_KEY
This type is comparable only with another TABLE_KEY or a char that has a length of 8 bytes (char(8)).
OBJECT_KEY
This type is comparable only with another OBJECT_KEY or a char that has a length of 16 bytes (char(16)).
If a logical key column is declared as SYSTEM_MAINTAINED, the DBMS Server assigns the values to that column. System maintained logical key columns cannot be updated. If a logical key column is declared as NOT SYSTEM_MAINTAINED, values must be assigned to the column.
In embedded SQL programs, if values are assigned using host variables, the host variables must be char(8)-comparable for TABLE_KEY columns, and char(16)-comparable variables for OBJECT_KEY columns.
Values can be assigned to logical keys, not system maintained, using a hex constant or a string literal. For the format of a hex constant, see String Literals.
Values assigned to TABLE_KEYs must be 8 bytes long. Values assigned to OBJECT_KEYs must be 16 bytes long. The following example inserts the value 1 into a TABLE_KEY column using a hex constant:
INSERT INTO test (tablekey) VALUES (TABLE_KEY(X'0000000000000001'));
The previous statement inserts 7 bytes containing 0, followed by 1 byte containing 1. The value is explicitly converted to a table key using the table_key conversion function.
The following example assigns the value 'abc' (padded to 8 characters for data type compatibility) to a logical key column:
INSERT INTO test (tablekey) VALUES (TABLE_KEY('abc'));
Null Value Assignments
A null can be assigned to a column of any data type if the column was defined as a nullable column. A null can also be assigned to a host language variable if there is an indicator variable associated with the host variable.
To ensure that a null is not assigned to a column, use the IFNULL function.
Last modified date: 08/29/2024