Assignment Operations
An assignment operation is an operation that 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.
When an assignment operation occurs, the data types of the assigned value and the receiving column or variable must either be the same or compatible. If the data types are compatible but not the same, OpenSQL performs a default type conversion.
All character data types are compatible with one another. A value from a string can be assigned to a date data item if the value in the string is formatted in a valid OpenSQL date input format. For information about valid input formats, see Absolute Date Input Formats in the chapter “OpenSQL Data Types.”
Money is compatible with all of the numeric and string types.
All numeric types are compatible with one another. For example, assuming that the following table is created:
create table emp
(name character(20),
salary float not null,
hiredate date not null);
then this insert statement
insert into emp (name, salary, hiredate)
values ('John Smith', 40000, date('10/12/98'));
assigns the varchar string literal, 'John Smith', to the character name column, the integer literal 40000 to the float salary column, and the varchar string literal '10/12/98' to the date column, hiredate.
Other examples of assignments are:
update emp set name = 'Mary Smith'
where name = 'Mary Jones';
create table emp2 (name2, hiredate2) as
select name, hiredate from emp;
In the following embedded OpenSQL example, the value in the name column is assigned to the variable, name_var, for each row that fulfills the where clause:
exec sql select name into :name_var from emp
where empno = 125;
The following sections present some specific guidelines for assignments into each of the general data types, as well as null assignments. In addition, see the Embedded SQL Companion Guide for information about which host language data types are compatible with which OpenSQL data types if you are assigning to a host language variable.
Character String Assignment
The character and varchar character types are compatible. Any character string can be assigned to any column or variable of character data type. (If an assignment results in truncation, OpenSQL returns a warning.) The result of the assignment depends on the types of the assignment string and the receiving column or variable:
• If a character string is assigned to a varchar column or variable, trailing blanks are trimmed from the character string before it is assigned.
If the length of the receiving string is shorter than the fixed length string, OpenSQL truncates the fixed length string (from the right end) and, if the assignment was to a variable, a warning condition is indicated. For a discussion of the SQLWARN indicators, see The SQL Communications Area (SQLCA) in the chapter “OpenSQL Features.”
• If a string is assigned to a column or variable that is shorter than the fixed‑length string, OpenSQL truncates the fixed‑length string from the right end. If a fixed‑length string is assigned to a fixed‑length column or variable that is longer than the fixed‑length string, OpenSQL pads it with blanks. If the assignment is to a variable and the string is truncated, a warning is indicated in the SQLCA.
Numeric Assignment
Any numeric data type can be assigned to any other numeric data type. In addition, a money value can be assigned to any numeric data type. OpenSQL may truncate leading zeros or all or part of the fractional part of a number if necessary. If it is necessary to truncate the non‑fractional part of a value (other than leading zeros), an overflow error results. When a float or decimal value is assigned to an integer column or variable, the fractional part is truncated.
Date Assignment
Date values can be assigned to a date column. In addition, a string literal, a string host variable, or a string column value can be assigned to a date column if its value conforms to the valid OpenSQL input formats for dates.
When assigning character strings to date columns in OpenSQL, specify the string using the date() function. For example:
insert into transaction_log (employee, trxtime,
trxid) values (user, date('now'), 42);
When assigning a date value to a character string, OpenSQL converts the date to the standard OpenSQL output date format. For more information about date output formats, see Date and Time Display Formats in the chapter “OpenSQL Data Types.”
Null Assignment
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. For more information about indicator variables, see Indicator Variables in the chapter “Embedded OpenSQL.”