|
|
|
Expressions are composed of various operators and operands that evaluate to a single value or a set of values. Some expressions do not use operators; for example, a column name is an expression. Expressions are used in many contexts, such as specifying values to be retrieved (in a select clause) or compared (in a where clause). For example:
select empname, empage from employee
where salary > 75000
In the preceding example, empname and empage are expressions representing the column values to be retrieved, salary is an expression representing a column value to be compared, and 75000 is an integer literal expression.
Expressions that contain aggregate functions can appear only in select and having clauses, and aggregate functions cannot be nested.
An expression can be enclosed in parentheses without affecting its value.
|
|
|
Case expressions provide a decoding capability that allows one expression to be transformed into another. Case expressions can appear anywhere that other forms of expressions can be used. There are two forms of case expressions:
A simple case expression looks like this:
case expr when expr1 then expr2 when expr3 then expr4... [else exprn] end
The initial case expression is compared in turn to the expressions in each when clause. The result of the case is the expression from the then clause corresponding to the first when clause whose expression is equal to the case expression. If none of the when expressions match the case expression, the result is the value of the expression from the else clause. If there is no else clause, the result is the null value.
The searched case expression syntax looks like this:
case when search_conditon1 then expr1 when search_expression2 then expr2...[else exprn] end
The search conditions of each when clause are evaluated in turn. The result of the case is the expression from the then clause corresponding to the first when clause whose search condition evaluates to true. If none of the when clause search conditions evaluate as true, the result is the value of the expression from the else clause. If there is no else clause, the result is the null value.
|
|
|
NULLIF and COALESCE are derivative functions of the case expression and can be defined in terms of the case expression. NULLIF returns the null value if its two parameters are equal; otherwise it returns the first parameter value. It can be defined as follows:
nullif(expr1, expr2) = case when expr1 = expr2 then null else expr1 end
COALESCE simply returns the first non-null value of an arbitrary list of parameters. It can be defined as follows:
coalesce(expr1, expr2) = case when expr1 is not null then expr1 else expr2end
coalesce(expr1, expr2, ..., exprn) = case when expr1 is not null then expr1
else coalesce(expr2, ..., exprn)
|
|
|
An alternative to the conversions outlined above is the cast expression. Cast expressions can appear anywhere that other forms of expression can and they coerce the source expression into the indicated data type.
They are specified as follows:
cast (expr as datatype)
where expr is the source value expression and datatype is any supported Ingres data type.
|
|
|
Sequence expressions return values from defined database sequences. A sequence expression consists of one of two different operators:
The next value operator returns the next available value from the referenced sequence. The current value operator returns the previous value returned from the sequence to the executing application. Note that the current value operator cannot be used in a transaction until a next value operator is executed on the same sequence. This prevents transactions from seeing sequence values returned to other executing applications.
Sequence expressions are typically used in INSERT or UPDATE statements to maintain columns that reflect some ordinal relationship with the creation of their containing rows. For example:
INSERT INTO T1 VALUES (:hv1, NEXT VALUE FOR mydb.t1seq, …)
or
INSERT INTO T2 SELECT col1, col2, t2seq.NEXTVAL, …FROM …
Sequence expressions can also be used in the select list of a SELECT statement, but not in a where clause, on clause, group by, or having clause.
A "next value" or "current value" expression on a particular sequence is evaluated once per row inserted by an INSERT statement, updated by an UPDATE statement, or added to the result set of a SELECT statement. If several occurrences of a "next value" or "current value" expression on the same sequence are coded in a single statement, only one value is computed for each row touched by the statement. If a "next value" expression and a "current value" expression are coded on the same sequence in the same statement, the "next value" expression is evaluated first, then the "current value" expression (assuring they return the same value), regardless of their order in the statement syntax.
|
|
|
In applications, sequences use logical locks that allow multiple transactions to retrieve and update the sequence value while preventing changes to the underlying sequence definition. The logical lock is held until the end of the transaction.