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 Functions. A primary is a signed or unsigned value derived from one of the items listed in the SQL syntax.

SQL syntax

{ColumnName | ROWID | {? | :DynamicParameter} |
  Function | Constant | (Expression)}

Or:

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

Or:

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

Or:

Expression1 | | Expression2

Or:

Expression

Component	Description
`+, –`	Unary plus and unary minus Unary minus changes the sign of the primary. The default is to leave the sign unchanged.
`ColumnName`	Name of a column from which a value is to be taken See "Names, Namespace and Parameters" for more information.
`ROWID`	Unique ID for each row stored in a table The rowid value can be retrieved through the `ROWID` pseudocolumn.
`?` `:DynamicParameter`	A placeholder for a dynamic parameter The value of the dynamic parameter is supplied at runtime.
`Function`	A computed value See "Functions" for more information.
`Constant`	A specific value See "Constants" for details.
`(Expression)`	Any expression enclosed in parentheses
`Expression1` `Expression2`	The specified expressions `Expression1` and `Expression2,` when used with the bitwise operators, can be of integer or binary types. The data types of the expressions must be compatible. See "Data Types" for more information.
`*`	Multiplies two primaries
`/`	Divides two primaries
`+`	Adds two primaries
`–`	Subtracts two primaries
`&`	Bitwise `AND` of the two operands Sets a bit to 1 if and only if both of the corresponding bits in `Expression1` and `Expression2` are 1. Sets a bit to 0 if the bits differ or both are 0.
`\|`	Bitwise `OR` of the two operands Sets a bit to 1 if one or both of the corresponding bits in `Expression1` and `Expression2` are 1. Sets a bit to 0 if both of the corresponding bits are 0.
`~`	Bitwise `NOT` of the operand Takes only one `Expression` and inverts each bit in the operand, changing all the ones to zeros and zeros to ones.
`^`	Exclusive `OR` of the two operands Sets the bit to 1 where the corresponding bits in its `Expression1` and `Expression2` are different and to 0 if they are the same. If one bit is 0 and the other bit is 1, the corresponding result bit is set to 1. Otherwise, the corresponding result bit is set to 0.
`\|\|`	Concatenate operator Concatenates `Expression1` and `Expression2`, where both expressions are character strings. Forms a new string value that contains the values of both expressions. See "CONCAT" for more information.

Description

Arithmetic operators can be used between numeric values. See "Numeric Data Types" for more information.
Arithmetic operators can also be used between datetime values and interval types. The result of a datetime expression is either a datetime data type or an interval data type.
Arithmetic operators cannot be applied to string values.
Elements in an expression are evaluated in the following order:
- 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. An example follows.
```
10 * 2 – 1 = 19 but 10 * (2 – 1) = 10
```
Type conversion, truncation, underflow, or overflow can occur when some expressions are evaluated. See "Data Types" for more information.
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 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.

Following are 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.