Expressions

3 Expressions

Expressions are used for the following purposes:

The select list of the INSERT...SELECT statement
A condition of the WHERE clause and the HAVING clause
The GROUP BY and ORDER BY clauses
The VALUES clause of the INSERT and MERGE statements
The SET clause of the UPDATE and MERGE statements

The following sections describe expressions in TimesTen:

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.

Subqueries

TimesTen supports subqueries in INSERT...SELECT, CREATE VIEW or UPDATE statements and in the SET clause of an UPDATE statement, in a search condition and as a derived table. TimesTen supports table subqueries and scalar subqueries. TimesTen does not support row subqueries. A subquery can specify an aggregate with a HAVING clause or joined table. It can also be correlated.

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

Table subquery:

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.

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);

Constants

A constant is a literal value.

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
}

Constant	Description
`IntegerValue`	A whole number compatible with `TT_INTEGER`, `TT_BIGINT` or `TT_SMALLINT` data types or an unsigned whole number compatible with the `TT_TINYINT` data type For example: 155, 5, -17
`FloatValue`	A floating-point number compatible with the `BINARY_FLOAT` or `BINARY_DOUBLE` data types Examples: .2E-4, 1.23e -4, 27.03, -13.1
`FloatingPointLiteral`	Floating point literals These are compatible with the `BINARY_FLOAT` and `BINARY_DOUBLE` data types. `f` or `F` indicates that the number is a 32-bit floating point number (of type `BINARY_FLOAT`). `d` or `D` indicates that the number is a 64-bit floating point number (of type `BINARY_DOUBLE`). For example: 123.23F, 0.5d
`FixedPointValue`	A fixed-point number compatible with the `BINARY_FLOAT`, `BINARY_DOUBLE` or `NUMBER` data types For example: 27.03
`CharacterString`	A character string compatible with `CHAR` or `VARCHAR2` data types String constants are delimited by single quotation marks. For example: 'DON''T JUMP!' Two single quotation marks in a row are interpreted as a single quotation mark, not as string delimiters or the empty string.
`NationalCharacterString`	A character string compatible with `NCHAR` or `NVARCHAR2` data types National string constants are preceded by an indicator consisting of either `N` or `n`, and delimited by single quotation marks. For example: N'Here''s how!' Two single quotation marks in a row are interpreted as a single quotation mark. The contents of a national string constant may consist of any combination of: ASCII characters UTF-8 encoded Unicode characters Escaped Unicode characters ASCII characters and UTF-8 encoded characters are converted internally to their corresponding UTF-16 format Unicode equivalents. Escaped Unicode characters are of the form `\uxxxx`, where `xxxx` is the four hexadecimal-digit representation of the Unicode character. For example: N'This is an \u0061' Is equivalent to: N'This is an a' The `\u` itself can be escaped with another `\`. The sequence `\\u` is always converted to `\u`. No other escapes are recognized.
`HexadecimalLiteral`	Hexadecimal literals Hexadecimal literals containing digits 0 - 9 and A - F (or a - f) are compatible with the `BINARY`, `VARBINARY`, `CHAR`, `VARCHAR2` and `BLOB` data types. A `HexadecimalLiteral` constant should be prefixed with the characters "`0x`." For example: 0xFFFAB0880088343330FFAA7 Or: 0x000A001231 Hexadecimal digits provided with an odd length are pre-fixed with a zero to make it even. For example, the value `0x123` is converted to `0x0123`. If you provide a character literal, the binary values of the characters are used. For example, the following demonstrates what is stored when inserting a hexadecimal literal and a character literal in a `VARBINARY` column `colbin` in table `tabvb`: 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. However, Oracle Database differs in that it only accepts character literals, such as '1234', and translates the character literal as a binary literal of 0x1234. As a result, `insert into tabvb values ('1234');` behaves differently between Oracle Database and TimesTen. Oracle Database does not accept 0x1234 as a hexadecimal literal.
`DateString`	A string of the format `YYYY-MM-DD` `HH:MI:SS` enclosed in single quotation marks (`'`) For example: '2007-01-27 12:00:00' The `YYYY` field must have a four-digit value. The `MM` and `DD` fields must have two-digit values. The only spaces allowed are trailing spaces (after the day field). The range is from `'-4713-01-01'` (January 1, 4712 BC) to `'9999-12-31'`, (December 31, 9999). The time component is not required. For example: '2007-01-27' For `TT_DATE` data types, the string is of format `YYYY-MM-DD` and ranges from `'1753-01-01'` to `'9999-12-31'`.
`DateLiteral`	Format: `DATE` `DateString` For example: DATE '2007-01-27' or DATE '2007-01-27 12:00:00' For `TT_DATE` data types, use the literal `TT_DATE`. For example: TT_DATE '2007-01-27'. Do not specify a time portion with the `TT_DATE` literal. The `DATE` keyword is case-insensitive. TimesTen also supports ODBC date-literal syntax. For example: {d '2007-01-27'}. See ODBC documentation for details.
`TimeString`	A string of the format `HH:MI:SS` enclosed in single quotation marks (`'`) For example: '20:25:30' The range is `'00:00:00'` to `'23:59:59'`, inclusive. Every component must be two digits. The only spaces allowed are trailing spaces (after the seconds field).
`TimeLiteral`	Format: `TIME` `TimeString` For example: TIME '20:25:30' The `TIME` keyword is case-insensitive. Usage examples: INSERT INTO timetable VALUES (TIME '10:00:00'); SELECT * FROM timetable WHERE col1 < TIME '10:00:00'; TimesTen also supports ODBC time literal syntax. For example: {t '12:00:00'}
`TimestampString`	A string of the format `YYYY-MM-DD HH:MI:SS [.FFFFFFFFF]` -enclosed in single quotation marks (') The range is from `'-4713-01-01'` (January 1, 4712 BC) to `'9999-12-31'` (December 31, 9999). The year field must be a four-digit value. All other fields except for the fractional part must be two-digit values. The fractional field can consist of zero to nine digits. For `TT_TIMESTAMP` data types, a string of format `YYYY-MM-DD HH:MI:SS[.FFFFFF]` enclosed in single quotation marks('). The range is from '`1753-01-01 00:00:00.000000'` to `'9999-12-31 23:59:59.999999'`. The fractional field can consist of zero to six digits. If you have a `CHAR` column called `C1`, and want to enforce the `TIME` comparison, you can do the following: SELECT * FROM testable WHERE C1 = TIME '12:00:00' In this example, each `CHAR` value from `C1` is converted into a `TIME` value before comparison, provided that values in `C1` conform to the proper `TIME` syntax.
`TimestampLiteral`	Format: `TIMESTAMP` `TimestampString` For example: TIMESTAMP '2007-01-27 11:00:00.000000' For `TIMESTAMP` data types, the fraction field supports from zero to nine digits of fractional seconds. For `TT_TIMESTAMP` data types, the fraction field supports from zero to six digits of fractional seconds. The `TIMESTAMP` keyword is case-insensitive. Use literal syntax to enforce `DATE/TIME/TIMESTAMP` comparisons for `CHAR` and `VARCHAR2` data types. TimesTen also supports ODBC timestamp literal syntax. For example: {ts '9999-12-31 12:00:00'}
`IntervalLiteral`	Format: `INTERVAL [+\-]` `CharacterString` `IntervalQualifier` For example: INTERVAL '8' DAY
`BINARY_FLOAT_INFINITY \| BINARY_DOUBLE_INFINITY`	Positive infinity `INF` (positive infinity) is an IEEE floating-point value that is compatible with the `BINARY_FLOAT` and `BINARY_DOUBLE` data types. Use the constant values `BINARY_FLOAT_INFINITY` or `BINARY_DOUBLE_INFINITY` to represent positive infinity.
`-BINARY_FLOAT_INFINITY \| -BINARY_DOUBLE_INFINITY`	Negative infinity `-INF` (negative infinity) is an IEEE floating-point value that is compatible with the `BINARY_FLOAT` and `BINARY_DOUBLE` data types. Use the constant values -`BINARY_FLOAT_INFINITY` and -`BINARY_DOUBLE_INFINITY` to represent negative infinity.
`BINARY_FLOAT_NAN` \| `BINARY_DOUBLE_NAN`	Non-numbers `NaN` ("not a number") is an IEEE floating-point value that is compatible with the `BINARY_FLOAT` and `BINARY_DOUBLE` data types. Use the constant values `BINARY_FLOAT_NAN` or `BINARY_DOUBLE_NAN` to represent `NaN`.

Format models

A format model is a character literal that describes the format of datetime and numeric data stored in a character string. When you convert a character string into a date or number, a format model determines how TimesTen interprets the string.

This section covers the following format models:

Number format models

Use number format models in the following functions:

In the TO_CHAR function to translate a value of NUMBER, BINARY_FLOAT, or BINARY_DOUBLE data type to VARCHAR2 data type.
In the TO_NUMBER function to translate a value of CHAR or VARCHAR2 data type to NUMBER data type.

The default american_america NLS language and territory setting is used.

A number format model is composed of one or more number format elements. The table lists the elements of a number format model. Negative return values automatically contain a leading negative sign and positive values automatically contain a leading space unless the format model contains the MI, S, or PR format element.

Table 3-1 Number format elements

Element	Example	Description
`,` (comma)	`9,999`	Returns a comma in the specified position. You can specify multiple commas in a number format model. Restrictions: A comma element cannot begin a number format model. A comma cannot appear to the right of the decimal character or period in a number format model.
`.` (period)	`99.99`	Returns a decimal point, which is a period (.) in the specified position. Restriction: You can specify only one period in a format model.
$	`$9999`	Returns value with leading dollar sign.
`0`	`0999` `9990`	Returns leading zeros. Returns trailing zeros.
`9`	`9999`	Returns value with the specified number of digits with a leading space if positive or with a leading minus if negative. Leading zeros are blank, except for a zero value, which returns a zero for the integer part of the fixed-point number.
`B`	`B9999`	Returns blanks for the integer part of a fixed-point number when the integer part is zero (regardless of zeros in the format model).
`C`	`C999`	Returns in the specified position the ISO currency symbol.
`D`	`99D99`	Returns the decimal character in the specified position. The default is a period (`.`). Restriction: You can specify only one decimal character in a number format model.
`EEEE`	`9.9EEEE`	Returns a value in scientific notation.
`G`	`9G999`	Returns the group separator in the specified position. You can specify multiple group separators in a number format model. Restriction: A group separator cannot appear to the right of a decimal character or period in a number format model.
`L`	`L999`	Returns the local currency symbol in the specified position.
`MI`	`999MI`	Returns negative value with a trailing minus sign (`-`). Returns positive value with a trailing blank. Restriction: The `MI` format element can appear only in the last position of a number format model.
`PR`	`999PR`	Returns negative value in angle brackets (`< >`). Returns positive value with a leading and trailing blank. Restriction: The `PR` format element can appear only in the last position of a number format model.
`RN`	`RN`	Returns a value as Roman numerals in uppercase.
`rn`	`rn`	Returns a value as Roman numerals in lowercase. Value can be an integer between 1 and 3999.
`S`	`S9999`	Returns negative value with a leading minus sign (`-`). Returns positive value with a leading plus sign (`+`).
`S`	`9999S`	Returns negative value with a trailing minus sign (`-`). Returns positive value with a trailing plus sign (`+`). Restriction: The `S` format element can appear only in the first or last position of a number format model.
`TM`	`TM`	The text minimum number format model returns (in decimal output) the smallest number of characters possible. This element is case insensitive. The default is `TM9`, which returns the number in fixed notation unless the output exceeds 64 characters. If the output exceeds 64 characters, then TimesTen automatically returns the number in scientific notation. Restrictions: You cannot precede this element with any other element. You can follow this element only with one 9 or one `E` or (`e`), but not with any combination of these. The following statement returns an error: `SELECT TO_NUMBER (1234, 'TM9e') FROM dual;`
`U`	`U9999`	Returns the euro or other dual currency symbol in the specified position.
`V`	`999V99`	Returns a value multiplied by 10ⁿ(and if necessary, rounds it up), where `n` is the number of 9s after the `V`.
`X`	`XXXX`	Returns the hexadecimal value of the specified number of digits. If the specified number is not an integer, then TimesTen rounds it to an integer. Restrictions: This element accepts only positive values or 0. Negative values return an error. You can precede this element only with 0 (which returns leading zeros) or `FM`. Any other elements return an error. If you specify neither 0 nor `FM` with `X`, then the return always has a leading blank.

Datetime format models

Use datetime format models in the following functions:

In the TO_CHAR, TO_DATE, and TO_TIMESTAMP functions to translate a character value that is in a format other than the default format for a datetime value.
In the TO_CHAR function to translate a datetime value that is in a format other than the default format into a string.

The total length of a datetime format model cannot exceed 22 characters.

The default american_america NLS language and territory setting are used.

Datetime format elements

A datetime format model is composed of one or more datetime format elements. Table 3-2 shows the datatime format elements. In addition:

For input format models, the format elements cannot appear more than once, and format elements that represent similar information cannot be combined. For example, you cannot use the SYYYY and the BC format elements in the same format string.
For DATE format elements, capitalization in a spelled-out word, abbreviation or Roman numeral, follows the capitalization in the corresponding format element. For example, the date format model DAY produces capitalized words, like MONDAY, whereas Day produces Monday, and day produces monday.

Table 3-2 Datetime format elements

Element	Description
`-/,.;:"text"`	Punctuation and quoted text are reproduced in the result.
`AD` `A.D.`	AD indicator with or without periods.
`AM` `A.M.`	Meridian indicator with or without periods.
`BC` `B.C.`	BC indicator with or without periods.
`CC` `SCC`	Valid in `TO_CHAR`. Century: If the last two digits of a 4-digit year are between 01 and 99 (inclusive), the century is one greater than the first two digits of the year. If the last two digits of a 4-digit year are 00, the century is the same as the first two digits of that year. For example, 2002 returns 21 and 2000 returns 20.
`D`	Day of week (1-7).
`DAY`	Name of day, padded with blanks to display width of widest name of day.
`DD`	Day of month (1-31).
`DDD`	Day of year.
`DL`	Long date format. In the default `AMERICAN_AMERICA` locale, this is equivalent to specifying the format '`fmDay, Month dd, yyyy`'. Restriction: Specify this format only with the `TS` element, separated by white space.
`DS`	Short date format In the default `AMERICAN_AMERICA` locale, this is equivalent to specifying the format `'MM/DD/RRRR'`. Restriction: Specify this format only with the `TS` element, separated by white space.
`DY`	Abbreviated name of day.
`FF[1-9]`	Valid in `TO_TIMESTAMP`. Fractional seconds. No radix character is printed. Use the `X` format element to add the radix character. Use the 1 to 9 numbers after FF to specify the number of digits in the fractional second part of the datatime value returned. If you do not specify a digit, the value is determined by the precision specified for the datetime data type, or by the data type's default precision.
`FM`	Returns a value with no leading or no trailing blanks.
`FX`	Returns exact matching between the character data and the format model.
`HH` `HH12`	Hour of day (1-12).
`HH24`	Hour of day (0-23).
`IW`	Valid in `TO_CHAR`. Calendar week of year (1-52 or 1-53), as defined by the ISO 8601 standard. A calendar week starts on Monday. The first calendar week of the year includes January 4. The first calendar week of the year may include December 29, 30, and 31. The last calendar week of the year may include January 1, 2, and 3.
`IYYY`	Valid in `TO_CHAR`. 4-digit year of the year containing the calendar week, as defined by the ISO 8601 standard.
`IYY` `IY` `I`	Valid in `TO_CHAR`. Last 3, 2, or 1 digit(s) of the year containing the calendar week, as defined by the ISO 8601 standard.
`J`	Julian day. This is the number of days since January 1, 4712 BC. Numbers specified with `J` must be integers.
`MI`	Minute (0-59).
`MM`	Month (01-12, where January = 01).
`MON`	Abbreviated name of month.
`MONTH`	Name of month padded with blanks to display width of the widest name of month.
`PM` `P.M.`	Meridian indicator with or without periods.
`Q`	Valid in `TO_CHAR`. Quarter of the year (1, 2, 3, 4). January = 1.
`RM`	Roman numeral month (I-XII. January = I).
`RR`	For 20th century dates in the 21st century using only two digits.
`RRRR`	Rounded year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as `RR`. If you do not want this functionality, then enter the 4-digit year.
`SS`	Second (0-59).
`SSSSS`	Seconds past midnight (0-86399).
`TS`	Returns a value in the short time format. Restriction: Specify this format only with the `DL` or `DS` element, separated by white space.
`WW`	Valid in `TO_CHAR`. Week of year (1-53) where week one starts on the first day of the year and continues to the seventh day of the year.
`W`	Valid in `TO_CHAR`. Week of month (1-5) where week one starts on the first day of the month and ends on the seventh.
`X`	Local radix character For example: 'HH:MI:SSXFF'
`Y,YYY`	Year with comma in this position
`YEAR` `SYEAR`	Valid in `TO_CHAR`. Year, spelled out. `S` prefixes BC dates with a minus sign (`-`).
`YYYY` `SYYYY`	4-digit year. `S` prefixes BC dates with a minus sign.
`YYY` `YY` `Y`	Last 3, 2, or 1 digit(s) of year.

Format model for ROUND and TRUNC date functions

The table lists the format models you can use with the ROUND and TRUNC date functions and the units to which they round and truncate dates. The default model DD returns the date rounded or truncated to the day with a time of midnight.

Format model	Rounding or truncating unit
`CC` `SCC`	Century If the last two digits of a four-digit year are between 01 and 99 (inclusive), then the century is one greater than the first two digits of that year. If the last two digits of a four-digit year are 00, then the century is the same as the first two digits of that year. For example, 2002 returns 21; 2000 returns 20.
`SYYYY` `YYYY` `YEAR` `SYEAR` `YYY` `YY` `Y`	Year (rounds up on July 1)
`IYYY` `IYY` `IY` `I`	ISO year
`Q`	Quarter (rounds up on the sixteenth day of the second month of the quarter)
`MONTH` `MON` `MM` `RM`	Name of month (rounds up on the sixteenth day)
`WW`	Same day of the week as the first day of the year
`IW`	Same day of the week as the first day of the ISO week, which is Monday
`W`	Same day of the week as the first day of the month
`DDD` `DD` `J`	Day of year
`DAY` `DY` `D`	Starting day of the week
`HH` `HH12` `HH24`	Hour
`MI`	Minute

Format model for TO_CHAR of TimesTen datetime data types

Use this format model when invoking the TO_CHAR function to convert a datetime value of TT_TIMESTAMP or TT_DATE. In addition, use this format model when invoking the TO_CHAR function to convert any numeric value other than NUMBER or ORA_FLOAT.

If a numeric value does not fit in the specified format, TimesTen truncates the value.
The format string cannot exceed 50 characters.
D always results in a decimal point. Its value cannot be changed with an NLS parameter.
If a float with an absolute value less than 1e-126 or greater than 1e126 is specified as input to the TO_CHAR function, TimesTen returns an error.

Format	Description
`DD`	Day of month (1-31)
`MM`	Month (1-12)
`MON`	Month (three character prefix)
`MONTH`	Month (full name blank-padded to 9 characters)
`YYYY`	Year (four digits)
`Y,YYY`	Year (with comma as shown)
`YYY`	Year (last three digits)
`YY`	Year (last two digits)
`Y`	Year (last digit)
`Q`	Quarter
`HH`	Hour (1-12)
`HH12`	Hour (1-12)
`HH24`	Hour (0-23)
`MI`	Minute (0-59)
`SS`	Second (0-59)
`FF`	Fractions of a second to a precision of six digits
`FFn`	Fractions of a second to the precision specified by `n`
`AM`	Meridian indicator
`A.M.`	Meridian indicator
`PM`	Meridian indicator
`P.M.`	Meridian indicator
`- / , . ; :`	Punctuation to be output
"text"	Text to be output
`9`	Digit
`0`	Leading or trailing zero
`.`	Decimal point
,	Comma
`EEEE`	Scientific notation
`S`	Sign mode
`B`	Blank mode If there are no digits, the string is filled with blanks.
`FM`	No-blank mode (fill mode) If this element is used, trailing and leading spaces are suppressed.
`$`	Leading dollar sign

CASE expressions

Specifies a conditional value. Both simple and searched case expressions are supported. The CASE expression can be specified anywhere an expression can be specified and can be used as often as needed.

Instead of using a series of IF statements, the CASE expression enables you to use a series of conditions that return the appropriate values when the conditions are met. With CASE, you can simplify queries and write more efficient code.

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:

Parameter	Description
`WHEN` `SearchCondition`	Specifies the search criteria. This clause cannot specify a subquery.
`WHEN` `CompExpression`	Specifies the operand to be compared.
Expression	Specifies the first operand to be compared with each `CompExpression`.
`THEN` `Expression1`	Specifies the resulting expression.
`ELSE` `Expression2`	If condition is not met, specifies the resulting expression. If no `ELSE` clause is specified, TimesTen adds an `ELSE` `NULL` clause to the expression.

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;

ROWID pseudocolumn

TimesTen assigns a unique id called a rowid to each row stored in a table. This rowid (called a ROWID pseudocolumn) has data type ROWID.

In TimesTen Scaleout, the ROWID pseudocolumn has a different meaning than in TimesTen Classic.

In TimesTen Scaleout:

TimesTen Scaleout uses ROWID to ensure uniqueness across all elements.
ROWID is the identifier of a specific copy of a row. If that copy is not available because the element that has the copy is not available, then you cannot access the row by ROWID. In this case, you should access the row by primary key.
Each copy of a row has different ROWID values. This is true for a duplicate distribution scheme where K-safety is set to 1 and for all tables (no matter what the distribution scheme is) where K-safety is set to 2. In these cases, when using ROWID based access, TimesTen Scaleout returns the value of the ROWID in the first data space.
Applications should not store ROWID values and try to use these values later.
ROWID values may change if the location of the data changes (through data redistribution).

For information on the ttGridAdmin dbDistribute command, see "Set or modify the distribution scheme of a database (dbDistribute)" in the Oracle TimesTen In-Memory Database Reference.
TimesTen Scaleout does not support the ROWID data type.

In TimesTen Classic:

You can examine a rowid by querying the ROWID pseudocolumn.
The ROWID is a pseudocolumn (not an actual column) and thus does not require database space. You cannot update, index, or drop ROWID.
The ROWID value persists throughout the life of the table row.
ROWID values persist through recovery, backup and restore operations. However copies of rows that are created as a result of replication or as a result of running ttMigrate to migrate rows out of the database and then back into the database or running ttBulkCp to copy rows out of the database and then back into the database have different ROWID values than the original rows.

For TimesTen Scaleout, see "Understanding ROWID in data distribution" in Oracle TimesTen In-Memory Database Scaleout User's Guide.

For TimesTen Classic, see "Expression specification" for more information on rowids. See "ROWID data type" for more information about the ROWID data type.

ROWNUM pseudocolumn

For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which the row was selected. The first row selected has a ROWNUM of 1, the second a ROWNUM of 2, and so on.

Use ROWNUM to limit the number of rows returned by a query as in this example:

SELECT * FROM employees WHERE ROWNUM < 10;

The order in which rows are selected depends on the index used and the join order. If you specify an ORDER BY clause, ROWNUM is assigned before sorting. However, the presence of the ORDER BY clause may change the index used and the join order. If the order of selected rows changes, the ROWNUM value associated with each selected row could also change.

For example, the following query may return a different set of employees than the preceding query if a different index is used:

SELECT * FROM employees WHERE ROWNUM < 10 ORDER BY last_name;

Conditions testing for ROWNUM values greater than a positive integer are always false. For example, the following query returns no rows:

SELECT * FROM employees WHERE ROWNUM > 1;

Use ROWNUM to assign unique values to each row of a table. For example:

UPDATE my_table SET column1 = ROWNUM;

If your query contains either FIRST NumRows or ROWS m TO n, do not use ROWNUM to restrict the number of rows returned. For example, the following query results in an error message:

SELECT FIRST 2 * FROM employees WHERE ROWNUM <1 ORDER BY employee_id;
2974: Using rownum to restrict number of rows returned cannot be combined with 
first N or rows M to N

Pseudocolumns in TimesTen Scaleout

Pseudocolumns are not actual columns in a table but behave like columns. A pseudocolumn is an assigned value used in the same context as a column, but is not stored.

You can perform select operations, but you cannot perform insert or update operations on a pseudocolumn.

Pseudocolumns in TimesTen Scaleout:

elementId#: An element stores a portion of the database. Use the elementId# pseudocolumn to determine the element from which you accessed the row. This pseudocolumn returns a NOT NULL TT_INTEGER data type.
replicaSetId#: Use this pseudocolumn to determine the replica set in which the row is stored. This pseudocolumn returns a NOT NULL TT_INTEGER data type.
dataspaceId#: Use this pseudocolumn to determine the data space in which the copy of the row resides. This pseudocolumn returns a NOT NULL TT_INTEGER data type.

Note:

For DML operations, use replicaSetId# instead of elementId#. This is also true for SELECT...FOR UPDATE.
For SELECT operations, use replicaSetId# unless you want to select rows from a specific element. In this case, use the TT_GridQueryExec (GLOBAL) optimizer hint with your SELECT statement. See "TT_GridQueryExec optimizer hint" for more information.

These sections illustrate how to use pseudocolumns:

Using pseudocolumns to locate data

This example illustrates how to use pseudocolumns to locate data. It determines the element to which the application is connected. It then issues a query on the customers table and returns the elementId#, replicaSetId#, and dataspaceId# where the data is located.

Command> SELECT elementid# FROM dual;
< 1 >
1 row found.

Command> SELECT elementId#,replicasetid#,dataspaceId#,cust_id,last_name,first_name
         FROM customers WHERE cust_id BETWEEN 910 AND 920
         ORDER BY cust_id, last_name, first_name;
< 3, 2, 1, 910, Riley, Tessa >
< 1, 1, 1, 911, Riley, Rashad >
< 1, 1, 1, 912, Riley, Emma >
< 1, 1, 1, 913, Rivera, Erin >
< 1, 1, 1, 914, Roberts, Ava >
< 1, 1, 1, 915, Roberts, Lee >
< 3, 2, 1, 916, Roberts, Clint >
< 5, 3, 1, 917, Robertson, Faith >
< 3, 2, 1, 918, Robinson, Miguel >
< 3, 2, 1, 919, Robinson, Mozell >
< 5, 3, 1, 920, Rodgers, Darryl >
11 rows found.

Working with pseudocolumns and duplicate tables

This example illustrates how to use pseudocolumns with duplicate tables. It uses the ttIsql describe command on the account_status table to validate the table has a duplicate distribution scheme. The example then issues a query from a connection that has been connected to element 1. The example returns the elementId#, replicasetId#, and dataspaceId# so the location of the data can be determined. The example repeats the same query from a connection that has been connected to element 2. The example illustrates the data is located on the element to which the application is connected and thus is present in every element of the database (duplicate distribution scheme).

Command> describe account_status;
 
Table SAMPLEUSER.ACCOUNT_STATUS:
  Columns:
   *STATUS                          NUMBER (2) NOT NULL
    DESCRIPTION                     VARCHAR2 (100) INLINE NOT NULL
  DUPLICATE
 
1 table found.
(primary key columns are indicated with *)
Command> SELECT elementId# FROM dual; 
< 1 >
1 row found.

Command> SELECT elementId#,replicaSetId#,dataspaceId#, *
         FROM account_status;
< 1, 1, 1, 10, Active - Account is in good standing >
< 1, 1, 1, 20, Pending - Payment is being processed >
< 1, 1, 1, 30, Grace - Automatic payment did not process successfully >
< 1, 1, 1, 40, Suspend - Account is in process of being disconnected >
< 1, 1, 1, 50, Disconnected - You can no longer make calls or receive calls >
5 rows found.

Issue the same query from a connection to element 2.

Command> SELECT elementid# from dual;
< 2 >
1 row found.

Command> SELECT elementId#,replicaSetId#,dataspaceId#, *
         FROM account_status;
< 2, 1, 2, 10, Active - Account is in good standing >
< 2, 1, 2, 20, Pending - Payment is being processed >
< 2, 1, 2, 30, Grace - Automatic payment did not process successfully >
< 2, 1, 2, 40, Suspend - Account is in process of being disconnected >
< 2, 1, 2, 50, Disconnected - You can no longer make calls or receive calls >
5 rows found.

Using pseudocolumns to locate the local element

This example illustrates how to use pseudocolumns to return information for the element to which the application is connected. It assumes you have created a grid with six data instances and K-safety set to 2. The purpose of this example is to show you how to identify the element id, replica set, and data space group for the element to which the application is connected.

In this example, your connection is connected to element 1. Selecting from the dual table returns the element id, replica set id, and data space id of the current local connection. In this example, element 1 is in replica set 1 and data space 1.

Command> SELECT elementId#,replicaSetId#,dataspaceId# FROM dual;
< 1, 1, 1 >
1 row found.

In this example, your connection is connected to element 3. Element 3 is in replica set 2 and in data space 1.

Command> SELECT elementId#,replicaSetId#,dataspaceId# FROM dual;
< 3, 2, 1 >
1 row found.

Displaying the element id associated with an instance

This example illustrates how to use the ttGridAdmin dbStatus -element command to display the element id associated with each instance. This command also gives the status of each element. (You must issue this command from the active management instance and you must issue it as the instance administrator.)

See "Monitor the status of a database (dbStatus)" in the Oracle TimesTen In-Memory Database Reference for more information.

$ ttGridAdmin dbStatus -element
Database database1 element level status as of Thu Apr  5 12:57:44 PDT 2018
 
Host            Instance Elem Status Date/Time of Event  Message
--------------- -------- ---- ------ ------------------- -------
host1           instance1    1 opened 2018-04-05 11:15:33
host2           instance2   2 opened 2018-04-05 11:15:33
host3           instance3   3 opened 2018-04-05 11:15:33
host4           instance4   4 opened 2018-04-05 11:15:33
host5           instance5   5 opened 2018-04-05 11:15:33
host6           instance6   6 opened 2018-04-05 11:15:33