 |
» |
|
|
|
An expression can consist of a primary or several primaries
connected by arithmetic operators. A primary is a signed
or unsigned value derived from one of the items listed in the SQL syntax
below.
Scope | |
SQL Data Manipulation Statements
SQL Syntax | |
[ + - ] { ColumnName USER :HostVariable [ [INDICATOR] :IndicatorVariable ] ? :LocalVariable :ProcedureParameter ::Built-inVariable AddMonthsFunction AggregateFunction Constant DateTimeFunction CurrentFunction LongColumnFunction StringFunction CASTFunction (Expression) TIDFunction } [{ * / + - || } [ + - ] { ColumnName :HostVariable [ [INDICATOR] :IndicatorVariable] ? :LocalVariable :ProcedureParameter ::Built-inVariable AddMonthsFunction AggregateFunction Constant DateTimeFunction CurrentFunction LongColumnFunction StringFunction CASTFunction (Expression) }] [...] Parameters | |
- +, -
designate unary plus and unary minus. Unary plus
assigns the primary a positive value. Unary minus
assigns the primary a negative value. Default is
positive.
- ColumnName
is the name of a column from which a value is
to be taken; column names are defined
in the "Names" chapter.
- USER
The keyword USER can be used as a character constant in several
locations as follows:
In a WHERE clause predicate when comparing it to a character
string, for example:
WHERE Owner = USER
WHERE Owner IN ('ALLUSERS', USER)
|
In the VALUES clause of the INSERT statement, for example: In a DEFAULT clause of a column definition, for example:
Owner CHAR(20) DEFAULT USER NOT NULL
|
In a SELECT list, returning a character string, for example: In an UPDATE SET clause, assigning a value to a character string,
for example:
USER evaluates to the current DBEUserID.
In ISQL, it evaluates to the
logon
name of the ISQL user. From an application program, it evaluates to the
logon
name running the program.
USER behaves
like a CHAR(20) constant, with trailing blanks if the
logon
name has fewer than 20 characters.
Note that if a column in your table is named USER, it must be preceded
with the table name for column values to be selected. The function
USER takes precedence over any column named USER.
- HostVariable
contains a value in an application program being
input to the expression.
- IndicatorVariable
names an indicator variable, whose value
determines whether the associated host variable contains a NULL value:
- > = 0
the value is not NULL
- < 0
the value is NULL (The value in the host variable will be ignored.)
- ?
is a place holder for a dynamic parameter in a prepared SQL statement in
an application program.
The value of the dynamic parameter is supplied at run time.
- LocalVariable
contains a value in a procedure.
- ProcedureParameter
contains a value that is passed into or out of a
procedure.
- Built-inVariable
is one of the following built-in variables
used for error handling:
The first six of these have the same meaning that they have as
fields in the SQLCA in application programs.
Note that in procedures, sqlerrd2 returns the number of rows processed for
all host languages. However, in application programs,
sqlerrd3 is used in COBOL, Fortran, and Pascal, while sqlerr2 is used
in C.
::activexact indicates whether a transaction is in progress
or not. For additional information, refer to the application
programming guides and to the chapter "Constraints, Procedures,
and Rules."
- AddMonthsFunction
returns a value that represents a DATE or DATETIME value with
a certain number of months added to it.
- AggregateFunction
is a computed value; aggregate functions are
defined in this chapter.
- Constant
is a specific value; constants are defined in this chapter.
- DateTimeFunction
returns a value that is a conversion of a date/time data type into an
INTEGER or CHAR value, or from a CHAR value.
- CurrentFunction
returns a value that represents the current DATE, TIME, or DATETIME.
- LongColumnFunction
returns information from a long column descriptor.
- StringFunction
returns a partial value or attribute of string data.
- CASTFunction
converts data from one data type to another.
- (Expression)
is one or more of the above primaries,
enclosed in parentheses.
- *
multiplies two primaries.
- /
divides two primaries.
- +
adds two primaries.
- -
subtracts two primaries.
- ||
concatenates two string operands.
- TIDFunction
returns the database address of a row (or rows for a BULK SELECT) of a
table or an updatable view. You cannot use mathematical operators
with this function except to compare it (using = or <>) to a value,
host variable, or dynamic parameter.
Description | |
Arithmetic operators can be used between numeric values, that is, those
with data types of FLOAT, REAL, INTEGER, SMALLINT,
or DECIMAL. Refer to the "Data Types" chapter for rules
governing the resulting precision and scale of DECIMAL operations.
Arithmetic operators can also be used between DATE, TIME, DATETIME, and
INTERVAL values. Refer to the "Data Types" chapter for rules on the
valid operations and the resulting data types.
Elements in an expression are evaluated in the following
order:
Aggregate functions and expressions in parentheses
are evaluated first.
Unary plusses and minuses are evaluated next.
and / operations are performed next.
The + and - operations are then performed.
You can enclose expressions in parentheses to control
the order of their evaluation. For example:
10 * 2 - 1 = 19, but
10 * (2-1) = 10
|
TO_INTEGER is the only date/time function that can be used in arithmetic
expressions.
When two primaries have the same data type, the result is of
that data type. For example, when an INTEGER is divided by
an INTEGER, the result is INTEGER. In such cases, the result will be
truncated.
If either arithmetic operand is the NULL value, then the result is the NULL value.
Arithmetic operators cannot be used to concatenate string values.
Use || to concatenate string operands.
Both operands of concatenation operator should be one of the following: CHAR (or VARCHAR, or Native CHAR, or Native VARCHAR), BINARY (or VARBINARY), but no mix of CHAR and BINARY.
If either concatenation operand is the NULL value, then the result of the concatenation is the NULL value.
If one concatenation operand is a variable length string (VARCHAR, Native VARCHAR, VARBINARY), then the result data type of the concatenation is a variable length string.
If both concatenation operands are fixed length string data type (CHAR, Native CHAR, BINARY), then the result of the concatenation is fixed length string.
The concatenation result will consist of the first operand followed by the second operand. The trailing blanks of the string value are preserved by concatenation regardless of the string's data types. The resultant string may be truncated on the right, if the length exceeds the maximum string length of 3996 bytes. If truncation occurs, a truncation warning is sent.
Type conversion, truncation, underflow, or overflow can occur when
some expressions are evaluated. For more information, refer to the chapter,
"Data Types."
The following expressions can evaluate to NULL:
Host variable with an indicator variable
A NULL value in an expression causes comparison operators and
other predicates to evaluate to unknown.
Refer to the "Search Conditions" chapter for more information on
evaluation of comparison operators and predicates containing NULL values.
The ? can be used as a host variable or dynamic parameter in an
expression as shown in the following examples:
In the WHERE clause of any SELECT statement:
SELECT *
FROM PurchDB.Orders
WHERE PartNumber = ?
AND OrderDate > ?
ORDER BY OrderDate
|
In the WHERE and SET clauses of an UPDATE statement:
UPDATE PurchDB.Parts
SET SalesPrice = ?
WHERE PartNumber = ?
|
In the WHERE clause of a DELETE statement:
DELETE FROM PurchDB.OrderItems
WHERE ItemDueDate
BETWEEN ? and ?
|
In the VALUES clause of an INSERT or a BULK INSERT statement.
In this example
each ? corresponds in sequential order to a column in the
PurchDB.OrderItems table:
BULK INSERT INTO PurchDB.OrderItems VALUES (?,?,?,?)
|
See the syntax descriptions for each DML statement, and for the PREPARE,
DESCRIBE, EXECUTE, and OPEN statements for details
of dynamic parameter usage.
Example | |
The result length of PartNumber || VendPartNumber is 32 in this example.
CREATE TABLE PurchDB.SupplyPrice
(Part Number CHAR(16) NOT CASE SENSITVE not null unique,
VendorNumber INTEGER
VendPartNumber CHAR(16) lang=german,
UnitPrice DECIMAL (10,2),
Delivery Days SMALLINT,
DiscountQty SMALLINT)
SELECT PartNumber || VendPartNumber, UnitPrice from PurchDB.SupplyPrice;
|
|
