HP 3000 Manuals HP 3000 Manuals
Expression
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
[REV BEG]
{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) }]
[REV END]
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:
VALUES (USER)
* 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:
SELECT USER, column1
* In an UPDATE SET clause, assigning a value
to a character string, for example:
SET Owner = USER
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:
* ::sqlcode
* ::sqlerrd2
* ::sqlwarn0
* ::sqlwarn1
* ::sqlwarn2
* ::sqlwarn6
* ::activexact
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.[REV BEG]
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.[REV END]
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 - 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.[REV BEG]
* 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."[REV END]
* If the value of an indicator variable is less than zero, the value
of the corresponding host variable is considered to be NULL.
_________________________________________________________________
NOTE To be consistent with the standard SQL and to support
portability of code, it is strongly recommended that you use
a -1 to indicate a NULL value. However, ALLBASE/SQL
interprets all negative indicator variable values as
indicating a NULL value in the corresponding host variable.
_________________________________________________________________
* The following expressions can evaluate to NULL:
* Host variable with an indicator variable
* Local variable
* Procedure parameter
* Column
* Add Months function
* DateTime function
* Aggregate function
* CAST function
* String function
* 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;