3 Expressions

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

Description

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

• 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 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 5, "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.

SQL syntax

[NOT] EXISTS | [NOT] IN (Subquery)
Expression {= | <> | > | >= | < | <= } [ANY | ALL] (Subquery)
Expression [NOT] IN (ValueList | Subquery)

Where ValueList is a list of constant expressions. Each constant expression specifies a constant value or an expression that evaluates to a constant value (such as a number, character string, or date). This includes support for bound values (? or :DynamicParameter), NULL, and calls to functions that return constant values.

Description

TimesTen supports queries with the characteristics listed in each section.

Table subqueries

• A subquery can appear in the WHERE clause or HAVING clause of any statement except one that creates a materialized view. Only one table subquery can be specified in a predicate. These predicates can be specified in a WHERE or HAVING clause, an OR expression within a WHERE or HAVING clause, or an ON clause of a joined table. They cannot be specified in a CASE expression, a materialized view, or a HAVING clause that uses the + operator for outer joins.

• A subquery can be specified in an EXISTS or NOT EXISTS predicate, a quantified predicate with ANY or ALL, or a comparison predicate. The allowed operators for both comparison and quantified predicates are: =, <, >, <=, >=, <>. The subquery cannot be connected to the outer query through a UNIQUE or NOT UNIQUE operator.

• Only one subquery can be specified in a quantified or comparison predicate. Specify the subquery as either the right operand or the left operand of the predicate, but not both.

• The subquery should not have an ORDER BY clause.

• FIRST NumRows is not supported in subquery statements.

• In a query specified in a quantified or comparison predicate, the underlying SELECT must have a single expression in the select list. In a query specified in a comparison predicate, if the underlying select returns a single row, the return value is the select result. If the underlying select returns no row, the return value is NULL. It is an error if the subquery returns multiple rows.

Scalar subqueries

A scalar subquery returns a single value.

• A nonverifiable scalar subquery has a predicate such that the optimizer cannot detect at compile time that the subquery returns at most one row for each row of the outer query. The subquery cannot be specified in an OR expression.

Examples

Examples of supported subqueries for a list of customers having at least one unshipped order:

SELECT customers.name FROM customers
  WHERE EXISTS (SELECT 1 FROM orders
    WHERE customers.id = orders.custid
    AND orders.status = 'unshipped');

SELECT customers.name FROM customers
  WHERE customers.id = ANY 
    (SELECT orders.custid FROM orders
  WHERE orders.status = 'unshipped');

SELECT customers.name FROM customers 
  WHERE customers.id IN 
    (SELECT orders.custid FROM orders 
  WHERE orders.status = 'unshipped');

In this example, list items are shipped on the same date as when they are ordered:

SELECT line_items.id FROM line_items 
  WHERE line_items.ship_date = 
    (SELECT orders.order_date FROM orders 
  WHERE orders.id = line_items.order_id);

SQL syntax

{IntegerValue | FloatValue |FloatingPointLiteral|
    FixedPointValue | 'CharacterString'|
    'NationalCharacterString' | HexadecimalLiteral |
    'DateString' | DateLiteral |'TimeString' | 
    TimeLiteral | 'TimestampString' | TimestampLiteral |
    IntervalLiteral | BINARY_FLOAT_INFINITY |
    BINARY_DOUBLE_INFINITY | -BINARY_FLOAT_INFINITY |
    -BINARY_DOUBLE_INFINITY | BINARY_FLOAT_NAN |
     BINARY_DOUBLE_NAN
}

155, 5, -17

.2E-4, 1.23e -4, 27.03, -13.1

123.23F, 0.5d

27.03

'DON''T JUMP!'

N'Here''s how!'

N'This is an \u0061'

N'This is an a'

0xFFFAB0880088343330FFAA7

0x000A001231

Command> insert into tabvb values (0x1234);
1 row inserted.
Command> insert into tabvb values ('1234');
1 row inserted.
Command> select colbin from tabvb;
< 1234 >
< 31323334 >
2 rows found.

'2007-01-27 12:00:00'

'2007-01-27'

DATE '2007-01-27' or DATE '2007-01-27 12:00:00'

TT_DATE '2007-01-27'. 

{d '2007-01-27'}. 

'20:25:30'

TIME '20:25:30'

INSERT INTO timetable VALUES (TIME '10:00:00');

SELECT * FROM timetable WHERE col1 < TIME '10:00:00';

{t '12:00:00'}

SELECT * FROM testable WHERE C1 = TIME '12:00:00'

TIMESTAMP '2007-01-27 11:00:00.000000'

{ts '9999-12-31 12:00:00'}

INTERVAL '8' DAY

SQL syntax

The syntax for a searched CASE expression is:

CASE
  {WHEN SearchCondition THEN Expression1}[…] 
   [ELSE Expression2]
END

The syntax for a simple CASE expression is:

CASE Expression 
  {WHEN CompExpression THEN Expression1}[…]
   [ELSE Expression2] 
END

Parameters

CASE has the parameters:

Description

You cannot specify the CASE expression in the value clause of an INSERT statement.

Examples

To specify a searched CASE statement that specifies the value of a color, use:

SELECT CASE 
  WHEN color=1 THEN 'red' 
  WHEN color=2 THEN 'blue' 
  ELSE 'yellow' 
END FROM cars;

To specify a simple CASE statement that specifies the value of a color, use the following.

SELECT CASE color 
  WHEN 1 THEN 'red' 
  WHEN 2 THEN 'blue' 
  ELSE 'yellow' 
END FROM cars;