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.