Expression specification

An expression specifies a value to be used in a SQL operation.

An expression can consist of a primary or several primaries connected by arithmetic operators, comparison operators, string or binary operators, bit operators or any of the functions described in this chapter. A primary is a signed or unsigned value derived from one of the items listed in the SQL syntax:

SQL syntax

{ColumnName | ROWID | {? | :DynamicParameter} |
  AggregateFunction | Constant | (Expression)}
or
[[+ |-] {ColumnName | SYSDATE | TT_SYSDATE|GETDATE() |
{? | :DynamicParameter} | AggregateFunction |
Constant | {~ | + | -} Expression}]
 [...]

or

Expression1 [& | | | ^ | + | / | * | - ] Expression2

or

Expression1 | | Expression2

or

Expression

Description

• Arithmetic operators can be used between numeric values. See "Numeric data types"

• Arithmetic operators can also be used between date-time values and interval types. The result of a date-time expression is either a date-time type or an interval type.

• Arithmetic operators cannot be applied to string values.

• Elements in an expression are evaluated in the following order:

  • Aggregate functions and expressions in parentheses.

  • Unary pluses and minuses.

  • The * and / operations.

  • The + and – operations.

  • Elements of equal precedence are evaluated in left-to-right order.

• You can enclose expressions in parentheses to control the order of their evaluation. For example:

  10 * 2 – 1 = 19 but 10 * (2 – 1) = 10
  

• Type conversion, truncation, underflow, or overflow can occur when some expressions are evaluated. See Chapter 1, "Data Types".

• If either operand in a numeric expression is NULL, the result is NULL.

• Since NVL takes two parameters, both designated as an "expression", TimesTen does not permit NULL in either position. If there is a NULL value in an expression, comparison operators and other predicates evaluate to NULL. See Chapter 4, "Search Conditions" for more information on evaluation of comparison operators and predicates containing NULL values. TimesTen permits inserting NULL, but in general INSERT takes only specific values, and not general expressions.

• The query optimizer and execution engine permit multiple rowid lookups when a predicate specifies a disjunct of rowid equalities or uses IN. For example, multiple fast rowid lookups are executed for:

  WHERE ROWID = :v1 OR ROWID = :v2
  

• or equivalently:

  WHERE ROWID IN (:v1, :v2)
  

• The ? or :DynamicParameter can be used as a dynamic parameter in an expression.

Examples

This example shows a dynamic parameter in the WHERE clause of any SELECT statement:

SELECT *
FROM purchasing.orders
WHERE partnumber = ?
AND ordernumber > ?
ORDER BY ordernumber;

This example shows a dynamic parameter in the WHERE and SET clauses of an UPDATE statement:

UPDATE purchasing.parts
SET salesprice = :dynamicparameter1
WHERE partnumber = :dynamicparameter2;

This example shows a dynamic parameter in the WHERE clause of a DELETE statement:

DELETE FROM purchasing.orderitems
WHERE itemnumber BETWEEN ? AND ?;

This example shows a dynamic parameter in the VALUES clause of an INSERT statement. In this example, both ? and :dynamicparameter are used where :dynamicparameter1 corresponds to both the second and fourth columns of the purchasing.orderitems table. Therefore, only four distinct dynamic parameters need to be passed to this expression with the second parameter used for both the second and fourth columns.

INSERT INTO purchasing.orderitems VALUES  
   (?,:dynamicparameter1,
    :dynamicparameter2,
    :dynamicparameter1,?);

This example demonstrates that both ? and :dynamicparameter can be used in the same SQL statement and shows the semantic difference between repeating both types of dynamic parameters.

Examples of bitwise operators:

Command> SELECT 0x183D & 0x00FF from dual;
< 003D >
1 row found.
Command> SELECT ~255 FROM dual;
< -256 >
1 row found.
Command> SELECT 0x08 | 0x0F FROM dual;
< 0F >
1 row found.