 |
» |
|
|
|
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 login name of the ISQL user. From an application program,
it evaluates to the login name running the program. USER behaves
like a CHAR(20) constant, with trailing blanks if the login 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 Chapter 4 “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. The * and / operations are performed next. The + and - οπ[epsiv]ρατιονσ αρ[epsiv] τη[epsiv]ν π[epsiv]ρφορµ[epsiv]δ.
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 Chapter 9 “Search Conditions” 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; |
