Assignment
An assignment operation is an operation which places a value in a column or variable. Assignment operations occur during the execution of append, replace, and retrieve 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 comparable. If they are not the same, the DBMS Server performs a default type conversion if the data types are comparable. If they are not comparable, you must convert the assignment value into a type which is the same or comparable with the receiving column or variable. For information about the type conversion functions, see
Data Type Conversion Functions.
All character string types (char, varchar, c, and text) are comparable with one another. Dates are comparable with string types if the format of the value in the string corresponds to a valid date input format. For information about valid date input formats, see
Absolute Date Input Formats on page 24.
All numeric types are comparable with one another. Money is comparable with all of the numeric and string types. For example, assuming that the following table is created:
create emp
(name=char(20),
salary=money not null,
hiredate=date not null);
this append statement
append to emp (name="John Smith", salary=40000,
hiredate="10/12/93")
assigns the varchar string literal, "John Smith", to the char column "name", the i4 literal 40000 to the money column "salary", and the varchar string literal "10/12/93" to the date column "hiredate".
The following assignment replaces an existing value in a table:
replace emp (name = "Mary Smith")
where name = "Mary Jones"
In the following embedded QUEL example, the value in the "name" column is assigned to the variable "name_var" for each row that fulfills the where clause.
retrieve (:name_var=emp.name)
where empno = 125
The following sections present guidelines for assigning values (including nulls) to each of the general data types. If you are assigning to a host language variable, see the Embedded QUEL Companion Guide for information about which host language data types are comparable with QUEL data types.
Character String
All character types are comparable with one another; you can assign any character string to any column or variable of character data type. The result of the assignment depends on the types of the assignment string and the receiving column or variable.
Numeric
You can assign any numeric data type to any other numeric data type. You can assign a money value to any numeric data type and a numeric value to the money data type. Numeric assignments have the following characteristics:
• The DBMS Server can truncate leading zeros or all or part of the fractional part of a number if necessary. If the non-fractional part of a value (other than leading zeros) is truncated, an overflow error results. (These errors are reported only if the -numeric_overflow flag is set. For information about the -numeric_overflow flag, see the quel command description in the Command Reference Guide.)
• When a float, float4, or money value is assigned to an integer column or variable, the fractional part is truncated.
Date
You can assign absolute date or interval column values to a date column. In addition, you can assign a string literal, a character string host variable, or a character string column value to a date column if its value conforms to the valid input formats for dates.
When you assign a date value to a character string, the DBMS Server converts the date to the display format. For more information about date display formats, see
Date and Time Display Formats on page 28.
You can use the date_trunc function to group all the dates within the same month or year, and so forth. For example:
date_trunc("month",date("23-oct-1998 12:33"))
returns "1-oct-1998", and
date_trunc("year",date("23-oct-1998"))
returns "1-jan-1998".
Truncation takes place in terms of calendar years and quarters ("1-jan," "1-apr," "1-jun" and "1-oct").
To truncate in terms of a fiscal year, you must offset the calendar date by the number of months between the beginning of your fiscal year and the beginning of the next calendar year ("6 mos" for a fiscal year beginning July 1, or "4 mos" for a fiscal year beginning September 1):
date_trunc("year",date+"4 mos") ‑ "4 mos"
Weeks start on Monday. The beginning of a week for an early January date falls into the previous year.
Using the Date_part
This function is useful in set functions and in assuring correct ordering in complex date manipulation. For example, if date_field contains the value
23-oct-1998,
date_part("month",date(date_field))
returns a value of "10" (representing October), and
date_part("day",date(date_field))
returns a value of "23".
Months are numbered 1 to 12, starting with January. Hours returned according to the 24-hour clock. Quarters are numbered 1 through 4. Week 1 begins on the first Monday of the year. Dates before the first Monday of the year are considered to be in week 0.
Null
You can assign a null to a column of any data type if the column was defined as a nullable column. You can also assign a null 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 on page 66.)
To ensure that a null is not assigned to a column, you can use
Ifnull.