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 10n (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