Skip Headers
Oracle® TimesTen In-Memory Database SQL Reference
Release 11.2.1

Part Number E13070-10
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

3 Expressions

Expressions are used for the following purposes:

ROWID specification

TimesTen assigns a unique ID called a rowid to each row stored in a table. The rowid has data type ROWID. You can examine a rowid by querying the ROWID pseudocolumn.

Because the ROWID pseudocolumn is not a real column, it does not require database space and cannot be updated, indexed or dropped.

The rowid value persists throughout the life of the table row, but the system can reassign the rowid to a different row after the original row is deleted. Zero is not a valid value for a rowid.

Rowids persists through recovery, backup and restore operations. They do not persist through replication, ttMigrate or ttBulkCp operations.

See "Expression specification" for more information on rowids. See "ROWID data type" for more information about the ROWID data type.

ROWNUM specification

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

Expression specification

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

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


SQL syntax

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

or

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

or

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

or

Expression1 | | Expression2

or

Expression
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. Column names are discussed in Chapter 2, "Names, Namespace and Parameters."
ROWID TimesTen assigns a unique ID called a rowid to 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.

AggregateFunction A computed value. See "Aggregate functions".
Constant A specific value. See "Constants".
(Expression) Any expression enclosed in parentheses.
Expression1

Expression2

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 Chapter 1, "Data Types."
* 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.
|| Concatenates Expression1 and Expression2, where both expressions are character strings. Forms a new string value that contains the values of both expressions. See also "CONCAT".


Description


Examples

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

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

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

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

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

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

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

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

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

Examples of bitwise operators:

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

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. It 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)

Description

TimesTen supports queries with the characteristics listed in each section.

Table subqueries

Scalar subqueries

A scalar subquery returns a single value.

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

Aggregate functions

Aggregate functions specify a value computed with data from a set of rows described in an argument. The argument, enclosed in parentheses, is an expression.

Aggregate functions can be specified in the select list or the HAVING clause. See "INSERT...SELECT" for more information. The value of the expression is computed using each row that satisfies the WHERE clause.

SQL syntax

{AVG   ({Expression | [ALL | DISTINCT] ColumnName})
 MAX   ({Expression | [ALL | DISTINCT] ColumnName | ROWID})
 MIN   ({Expression | [ALL | DISTINCT] ColumnName | ROWID})
 SUM   ({Expression | [ALL | DISTINCT] ColumnName})
 COUNT ({ * | [ALL | DISTINCT] ColumnName | ROWID})
}
Component Description
Expression Specifies an argument for the aggregate function. The expression itself cannot be an aggregate function.
AVG Computes the arithmetic mean of the values in the argument. NULL values are ignored. AVG can be applied only to numeric data types.
MAX Finds the largest of the values in the argument (ASCII comparison for alphabetic types). NULL values are ignored. MAX can be applied to numeric, character, and BINARY data types.
MIN Finds the smallest of the values in the argument (ASCII comparison for alphabetic types). NULL values are ignored. MIN can be applied to numeric, character, and BINARY data types.
SUM Finds the total of all values in the argument. NULL values are ignored. SUM can be applied to numeric data types only.
COUNT * Counts all rows that satisfy the WHERE clause, including rows containing NULL values. The data type of the result is TT_INTEGER. For more information on the number of rows in a table, see the description for the NUMTUPS field in "SYS.TABLES" in Oracle TimesTen In-Memory Database System Tables and Limits Reference.
COUNT

ColumnName

Counts all rows in a specific column. Rows containing NULL values are not counted. The data type of the result is TT_INTEGER. For more information on the number of rows in a table, see the description for the NUMTUPS field in "SYS.TABLES" in Oracle TimesTen In-Memory Database System Tables and Limits Reference.
ALL Includes any duplicate rows in the argument of an aggregate function. If neither ALL nor DISTINCT is specified, ALL is assumed.
DISTINCT Eliminates duplicate column values from the argument of an aggregate function. Can be specified for more than one column.

Description

See Chapter 1, "Data Types" for information about:

Examples

Calculate the average salary for employees in the HR schema. Use CAST to cast the average as the data type of the column:

Command> SELECT CAST(AVG (salary) AS NUMBER (8,2)) FROM employees;
< 6461.68 >

Calculate the MAX salary for employees in the HR schema:

Command> SELECT MAX (salary) FROM employees;
< 24000 >
1 row found.

The example uses DESCRIBE to show the data type that is returned when using the SUM aggregate. The aggregates table is created and columns with different data types are defined:

Command> CREATE TABLE aggregates (col1 TT_TINYINT, col2 TT_SMALLINT, 
       > col3 TT_INTEGER, col4 TT_BIGINT, col5 NUMBER (4,2), 
       > col6 TT_DECIMAL (6,2), col7 BINARY_FLOAT, col8 BINARY_DOUBLE);
Command> DESCRIBE SELECT SUM (col1) FROM aggregates;
Prepared Statement:
  Columns:
    EXP                             TT_INTEGER
Command> DESCRIBE SELECT SUM (col2) FROM aggregates;
Prepared Statement:
Columns:
    EXP                             TT_INTEGER
Command> DESCRIBE SELECT SUM (col3) FROM aggregates;
Prepared Statement:
Columns:
    EXP                             TT_INTEGER
Command> DESCRIBE SELECT SUM (col4) FROM Aggregates;
Prepared Statement:
Columns:
    EXP                             TT_BIGINT
Command> DESCRIBE SELECT SUM (col5) FROM aggregates;
Prepared Statement:
Columns:
    EXP                             NUMBER
Command> DESCRIBE SELECT SUM (col6) FROM aggregates;
Prepared Statement:
Columns:
    EXP                             TT_DECIMAL (40,2)
Command> DESCRIBE SELECT SUM (col7) FROM aggregates;
Prepared Statement:
Columns:
    EXP                             BINARY_FLOAT
Command> DESCRIBE SELECT SUM (col8) FROM Aagregates;
Prepared Statement:
  Columns:
    EXP                             BINARY_DOUBLE

Constants

A constant is a literal value.

SQL syntax

{IntegerValue | FloatValue |FloatingPointLiteral|
    FixedPointValue | 'CharacterString'|
    'NationalCharacterString' | 0xHexadecimalString |
    '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 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 hex-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.

HexadecimalString A string of hexadecimal digits 0 - 9 and A - F (or a - f) compatible with the BINARY, VARBINARY, CHAR and VARCHAR2 data types. A HexadecimalString constant must be prefixed with the characters "0x." For example:
0xFFFAB0880088343330FFAA7

or

0x000A001231
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 4-digit value. The MM and DD fields must have 2-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'.

If you are using TimesTen type mode, see Oracle TimesTen In-Memory Database Release 6.0.3 documentation for information about DateString.

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.

If you are using TimesTen type mode, see Oracle TimesTen In-Memory Database Release 6.0.3 documentation for information about DateLiteral.

TimeString A string of the format HH:MM: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 4-digit value. All other fields except for the fractional part must be 2-digit values. The fractional field can consist of 0 to 9 digits. For TT_TIMESTAMP data types, a string of format YYYY-MM-DD HH:MM: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 0 to 6 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.

If you are using TimesTen type mode, see Oracle TimesTen In-Memory Database Release 6.0.3 documentation for information on TimestampString.

TimestampLiteral Format: TIMESTAMP TimestampString

For example:

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

For TIMESTAMP data types, the fraction field supports from 0 to 9 digits of fractional seconds. For TT_TIMESTAMP data types, the fraction field supports from 0 to 6 digits of fractional seconds.

The TIMESTAMP keyword is case-insensitive.

Literal syntax can be used if you want 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'}

If you are using TimesTen type mode, see Oracle TimesTen In-Memory Database Release 6.0.3 documentation for information about TimestampLiteral.

IntervalLiteral Format: INTERVAL [+\-] CharacterString IntervalQualifier.

For example INTERVAL '8' DAY

BINARY_FLOAT_INFINITY|

BINARY_DOUBLE_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

-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

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 ("not a number").


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.


Number format models

Use number format models in the following functions:

Number format elements

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.

The default american_america NLS language and territory setting is used.

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:

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

The default american_america NLS language and territory setting is used.


Datetime format elements

A datetime format model is composed of one or more datetime format elements.

Table 3-2 Datetime format elements

Element Description

-/,.;:"text"

Punctuation and quoted text is 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.

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

Returns a value in the 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

Returns a value in the 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.

FM

Returns a value with no leading or trailing blanks.

FX

Requires exact matching between the character data and the format model.

HH

Hour of day (1-12).

HH24

Hour of day (0-23).

J

Julian day: The number of days since January 1, 4712 BC. Numbers specified with J must be integers.

MI

Minute (0-59).

MM

Month (01-12. January = 01).

MON

Abbreviated name of month.

MONTH

Name of month padded with blanks to display width of the widest name of month.

RM

Roman numeral month (I-XII. January = I).

RR

Stores 20th century dates in the 21st century using only two digits.

RRRR

Rounds 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.

X

Local radix character.

Example: 'HH:MI:SSXFF'.

Y,YYY

Year with comma in this position.

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 2 digits of a 4-digit year are between 01 and 99 (inclusive), then the century is one greater than the first 2 digits of that year.

If the last 2 digits of a 4-digit year are 00, then the century is the same as the first 2 digits of that year.

For example, 2002 returns 21; 2000 returns 20.

SYYYY

YYYY

YEAR

SYEAR

YYY

YY

Y

Year. All year output 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.

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 6 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.


ABS

The ABS function returns the absolute value of Expression.

SQL syntax

ABS(Expression)

Parameters

ABS has the parameter:

Parameter Description
Expression Operand or column can be any numeric data type. Absolute value of Expression is returned.

Description

Examples

Create table abstest and define columns with type BINARY_FLOAT and TT_INTEGER. Insert values -BINARY_FLOAT_INFINITY and -10. Call ABS to return the absolute value. You see INF and 10 are the returned values:

Command> CREATE TABLE abstest (col1 BINARY_FLOAT, col2 TT_INTEGER);
Command> INSERT INTO abstest VALUES 
       > (-BINARY_FLOAT_INFINITY, -10);
1 row inserted.
Command> SELECT ABS (col1) FROM abstest;
< INF >
1 row found.
Command> SELECT ABS (col2) FROM abstest;
< 10 >
1 row found.

ADD_MONTHS

The ADD_MONTHS function returns the date resulting from date plus integer months.

SQL syntax

ADD_MONTHS(date,integer)

Parameters

ADD_MONTHS has the parameters:

Parameter Description
date A datetime value or any value that can be implicitly converted to DATE.
integer An integer or any value that can be implicitly converted to an integer.

Description

Examples

Call the ADD_MONTHS function to add 1 month to date January 31, 2007. The last day of February is returned.

Command> SELECT ADD_MONTHS (DATE '2007-01-31', 1) FROM dual;
< 2007-02-28 00:00:00 >
1 row found.

ADD_MONTHS returns data type DATE if date is of type TIMESTAMP:

Command> DESCRIBE SELECT ADD_MONTHS (TIMESTAMP '2007-01-31
       > 10:00:00', 1) FROM dual;
Prepared Statement:
  Columns:
    EXP                             DATE NOT NULL

Use the HR schema to select the first 5 rows of the employees table, showing employee_id, last_name and hire_date. Create new table temp_hire_date using the CREATE TABLE ... AS SELECT statement. Call ADD_MONTHS to add 23 months to the original hire_date.

Command> SELECT FIRST 5 employee_id, last_name, hire_date FROM employees;
< 100, King, 1987-06-17 00:00:00 >
< 101, Kochhar, 1989-09-21 00:00:00 >
< 102, De Haan, 1993-01-13 00:00:00 >
< 103, Hunold, 1990-01-03 00:00:00 >
< 104, Ernst, 1991-05-21 00:00:00 >
5 rows found.
Command> CREATE TABLE temp_hire_date (employee_id, last_name,
        > hire_date) AS SELECT FIRST 5 employee_id, last_name,
        > ADD_MONTHS (hire_date, 23) FROM employees;
5 rows inserted.
Command> SELECT * FROM temp_hire_date;
< 100, King, 1989-05-17 00:00:00 >
< 101, Kochhar, 1991-08-21 00:00:00 >
< 102, De Haan, 1994-12-13 00:00:00 >
< 103, Hunold, 1991-12-03 00:00:00 >
< 104, Ernst, 1993-04-21 00:00:00 >
5 rows found.

ASCIISTR

The ASCIISTR function takes as its argument, either a string or an expression that resolves to a string, in any character set, and returns the ASCII version of the string in the database character set. Non-ASCII characters are converted to Unicode escapes.

SQL syntax

ASCIISTR ([N]'String')

Parameters

ASCIISTR has the parameter:

Parameter Description
[N]'String' The string passed to the ASCIISTR function. The string can be in any character set. The ASCII version of the string in the database character set is returned. Specify N if you want to pass the string in UTF-16 format.

Description

The ASCIISTR function allows you to see the representation of a string value that is not in the database character set.

Examples

The following example invokes the ASCIISTR function passing as an argument the string 'Aäa' in UTF-16 format. The ASCII version is returned in the WE8ISO8859P1 character set. The non-ASCII character ä is converted to Unicode encoding value:

Command> connect "dsn=test; ConnectionCharacterSet= WE8ISO8859P1";
Connection successful: DSN=test;UID=user1;DataStore=/datastore/user1/test;
DatabaseCharacterSet=WE8ISO8859P1;
ConnectionCharacterSet=WE8ISO8859P1;PermSize=32;TypeMode=0;
(Default setting AutoCommit=1)
Command> SELECT ASCIISTR (n'Aäa') FROM dual;
< A\00E4a >
1 row found.

CASE

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

Instead of using a series of IF statements, case expression allows you to use a series of conditions that return the appropriate values when the conditions are met. With CASE expression, 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

CASE expression cannot be specified 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:

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

CAST

Allows you to convert data of one type to another type. CAST can be used wherever a constant can be used. CAST is useful in specifying the exact data type for an argument. This is especially true for unary operators like '-' or functions with one operand like TO_CHAR or TO_DATE.

A value can only be CAST to a compatible data type, with the exception of NULL. NULL can be cast to any other data type. CAST is not needed to convert a NULL to the desired target type in an insert select.

The following conversions are supported:

SQL syntax

CAST
  ( {Expression | NULL} AS DataType )

Parameters

CAST has the parameters:

Parameter Description
Expression Specifies the value to be converted.
AS DataType Specifies the resulting data type.

Description

Examples

INSERT INTO t1 VALUES(TO_CHAR(CAST(? AS REAL)));
SELECT CONCAT(x1, CAST (? AS CHAR(10))) FROM t1;        
SELECT * FROM t1 WHERE CAST (? AS INT)=CAST(? AS INT);

CHR

The CHR function returns the character having the specified binary value in the database character set.

SQL syntax

CHR(n)

Parameters

CHR has the parameter:

Parameter Description
n The binary value in the database character set. The character having this binary value is returned. The result is of type VARCHAR2.

Description

Examples

The following example is run on an ASCII-based machine with the WE8ISO8859P1 character set.

Command> SELECT CHR(67)||CHR(65)||CHR(84) FROM dual;
< CAT >
1 row found.

CEIL

The CEIL function returns the smallest integer greater than or equal to Expression.

SQL syntax

CEIL(Expression)

Parameters

CEIL has the parameter:

Parameter Description
Expression Operand or column can be any numeric data type.

Description

Examples

Sum the commission_pct for employees in the employees table, and then call CEIL to return the smallest integer greater than or equal to the value returned by SUM. You see the value returned by the SUM function is 7.8 and the value returned by the CEIL function is 8.

Command> SELECT SUM (commission_pct) FROM employees;
< 7.8 >
1 row found.
Command> SELECT CEIL (SUM (commission_pct)) FROM employees;
< 8 >
1 row found.

COALESCE

The COALESCE function returns the first non-null expression in the expression list. If all occurrences of expression evaluate to NULL, then the function returns NULL.

SQL syntax

COALESCE(Expression1, Expression2 [,...])

Parameters

COALESCE has the parameters:

Parameter Description
Expression1, Expression2 [,...] The expressions in the expression list. The first non-null expression in the expression list is returned.

Each expression is evaluated in order and there must be at least 2 expressions.


Description

Examples

The example illustrates the use of the COALESCE expression. The COALESCE expression is used to return the commission_pct for the first 10 employees with manager_id = 100. If the commission_pct is NOT NULL, then the original value for commission_pct is returned. If commission_pct is NULL, then 0 is returned.

Command> SELECT FIRST 10 employee_id, COALESCE (commission_pct, 0) FROM employees
       > WHERE manager_id = 100;
< 101, 0 >
< 102, 0 >
< 114, 0 >
< 120, 0 >
< 121, 0 >
< 122, 0 >
< 123, 0 >
< 124, 0 >
< 145, .4 >
< 146, .3 >
10 rows found.

CONCAT

The CONCAT function concatenates one character string with another to form a new character string.

SQL syntax

CONCAT(Expression1, Expression2)

Parameters

CONCAT has the parameters:

Parameter Description
Expression1 A CHAR, VARCHAR2, NCHAR or NVARCHAR2 expression.
Expression2 A CHAR, VARCHAR2, NCHAR or NVARCHAR2 expression.

Description

Examples

The following example concatenates first names and last names.

Command> SELECT CONCAT(CONCAT(first_name, ' '), last_name), salary FROM employees;
< Steven King, 24000 >
< Neena Kochhar, 17000 >
< Lex De Haan, 17000 >
< Alexander Hunold, 9000 >
...
107 rows found.

The following example concatenates column id with column id2. In this example, the result type is NCHAR(40).

Command> CREATE TABLE cat (id CHAR (20), id2 NCHAR (20));
Command> INSERT INTO cat VALUES ('abc', 'def');
1 row inserted.
Command> SELECT CONCAT (id,id2) FROM cat;
< abc                 def                  >
1 row found.

The description of the || operator is in "Expression specification".


DECODE

The DECODE function compares an expression to each search value one by one. If the expression is equal to the search value, then the result value is returned. If no match is found, then the default value (if specified) is returned. Otherwise NULL is returned.

SQL syntax

DECODE(Expression, {SearchValue, Result [,...])} [,Default])

Parameters

DECODE has the parameters:

Parameter Description
Expression The expression that is compared to the search value.
SearchValue An expression is compared to one or more search values.
Result If the expression is equal to a SearchValue, then the specified Result value is returned.
Default If no match is found, the default value is returned. Default is optional. If Default is not specified and no match is found, then NULL is returned.

Description

If an expression is NULL, then the NULL expression equals a NULL search value.

Examples

The following example invokes the DECODE function. In the locations table, if the column country_id is equal to 'IT', then the function returns 'Italy'. If the country_id is equal to 'JP', then the function returns 'Japan'. If the country_id is equal to 'US', then 'United States' is returned. If the country_id is not equal to 'IT' or 'JP' or 'US', then the function returns 'Other'.

Command> SELECT location_id,
       > DECODE (country_id, 'IT', 'Italy',
       >                           'Other')
       > FROM locations WHERE location_id < 2000;
LOCATION_ID, EXP
< 1000, Italy >
< 1100, Italy >
< 1200, Japan >
< 1300, Japan >
< 1400, United States >
< 1500, United States >
< 1600, United States >
< 1700, United States >
< 1800, Other >
< 1900, Other >
10 rows found.

EXTRACT

The EXTRACT function extracts and returns the value of a specified datetime field from a datetime or interval value expression as a NUMBER data type. This function can be useful for manipulating datetime field values in very large tables.

If you are using TimesTen type mode, see the Oracle TimesTen In-Memory Database Release 6.0.3 documentation for information about the EXTRACT function.

SQL syntax

EXTRACT (DateTimeField FROM IntervalExpression | DateTimeExpression)

Parameters

EXTRACT has the following parameters:

Parameter Description
DateTimeField The field to be extracted from IntervalExpression or DateTimeExpression. Accepted fields are YEAR, MONTH, DAY, HOUR, MINUTE or SECOND.
IntervalExpression An interval result.
DateTimeExpression A datetime expression. For example, TIME, DATE, TIMESTAMP.

Description

Examples

The following example extracts the second field out of the interval result sysdate-t1.createtime.

SELECT EXTRACT(SECOND FROM sysdate-t1.createtime) FROM t1;

The following example extracts the second field out of sysdate from the dual system table.

Command> SELECT EXTRACT (SECOND FROM sysdate) FROM dual;
< 20 >
1 row found.

FLOOR

The FLOOR function returns the largest integer equal to or less than Expression.

SQL syntax

FLOOR (Expression)

Parameters

FLOOR has the parameter:

Parameter Description
Expression Operand or column can be any numeric data type.

Description

Examples

Sum the commission_pct for employees in the employees table. Then call FLOOR to return the largest integer equal to or less than the value returned by SUM. You see the value returned by the SUM function is 7.8 and the value returned by the FLOOR function is 7:

Command> SELECT SUM (commission_pct) FROM employees;
< 7.8 >
1 row found.
Command> SELECT FLOOR (SUM (commission_pct)) FROM employees;
< 7 >
1 row found.

GREATEST

The GREATEST function returns the greatest of the list of one or more expressions.

SQL syntax

GREATEST (Expression [,...])

Parameters

GREATEST has the parameter:

Parameter Description
Expression [,...] List of one or more expressions that is evaluated to determine the greatest expression value. Operand or column can be numeric, character or date. Each expression in the list must be from the same data type family.

Description

Use the GREATEST function to return the string with the greatest value:

Command> SELECT GREATEST ('GREAT', 'GREATER', 'GREATEST') FROM dual;
< GREATEST >
1 row found.

Use the GREATEST function to return the numeric expression with the greatest value. In this example, BINARY_DOUBLE is the data type with the highest numeric precedence, so arguments are implicitly converted to BINARY_DOUBLE before the comparison and the data type BINARY_DOUBLE is returned:

Command> SELECT GREATEST (10, 10.55, 10.1D) FROM dual;
< 10.5500000000000 >
1 row found.

Use the DESCRIBE command to confirm the data type returned is BINARY_DOUBLE:

Command> DESCRIBE SELECT GREATEST (10, 10.55, 10.1D) FROM dual;

Prepared Statement:
  Columns:
    EXP                             BINARY_DOUBLE NOT NULL

Use the GREATEST function to return the DATE expression with the greatest value. DATE and TIMESTAMP are in the same date family.

Command> SELECT GREATEST (DATE '2007-09-30',
       > TIMESTAMP '2007-09-30:10:00:00') FROM dual;
< 2007-09-30 10:00:00 >
1 row found.

Attempt to use the GREATEST function to return the greatest value in the list of TT_DATE and TT_TIMESTAMP expressions. You see an error because TT_DATE and TT_TIMESTAMP are in different date subfamilies and cannot be used in the same list of expressions.

Command> SELECT GREATEST (TT_DATE '2007-09-30', TT_TIMESTAMP
       > '2007-09-30:10:00:00') FROM dual;
2817: Invalid data type TT_TIMESTAMP for argument 2 for function GREATEST
The command failed.

Use the GREATEST function to return the TT_DATE expression with the greatest value.

Command> SELECT GREATEST (TT_DATE '2007-09-30',
       > TT_DATE '2007-09-29', TT_DATE '2007-09-28') FROM dual;
< 2007-09-30 >
1 row found.

LEAST

The LEAST function returns the smallest of the list of one or more expressions.

SQL syntax

LEAST (Expression [,...])

Parameters

LEAST has the parameter:

Parameter Description
Expression [,...] List of one or more expressions that is evaluated to determine the smallest expression value. Operand or column can be numeric, character, or date. Each expression in the list must be from the same data type family.

Description

Use the LEAST function to return the string with the smallest value:

Command> SELECT LEAST ('SMALL','SMALLER','SMALLEST') FROM dual;
< SMALL >
1 row found.

Use the LEAST function to return the numeric expression with the smallest value. In this example, NUMBER is the data type with the highest numeric precedence, so arguments are implicitly converted to NUMBER before the comparison and the data type NUMBER is returned. First describe the table leastex to see the data types defined for columns col1 and col2. Then SELECT * from leastex to see the data. Then invoke the LEAST function.

Command> DESCRIBE leastex;

Table SAMPLEUSER.LEASTEX:
  Columns:
    COL1                            NUMBER (2,1)
    COL2                            TT_BIGINT

1 table found.
(primary key columns are indicated with *)
Command> SELECT * FROM leastex;
< 1.1, 1 >
1 row found.
Command> SELECT LEAST (Col2,Col1) from leastex;
< 1 >
1 row found.

Use the DESCRIBE command to confirm that the data type returned is NUMBER:

Command> DESCRIBE SELECT LEAST (Col2,Col1) FROM leastex;

Prepared Statement:
  Columns:
    EXP                             NUMBER

Use the LEAST function to return the DATE expression with the smallest value. DATE and TIMESTAMP are in the same date family.

Command> SELECT LEAST (DATE '2007-09-17', 
       > TIMESTAMP '2007-09-17:10:00:00') FROM dual;
< 2007-09-17 00:00:00 >
1 row found.

Attempt to use the LEAST function to return the smallest value in the list of TT_DATE and TT_TIMESTAMP expressions. You see an error because TT_DATE and TT_TIMESTAMP are in different date subfamilies and cannot be used in the same list of expressions.

Command> SELECT LEAST (TT_DATE '2007-09-17',
       > TT_TIMESTAMP '2007-09-17:01:00:00') FROM dual;
2817: Invalid data type TT_TIMESTAMP for argument 2 for function LEAST
The command failed.

Use the LEAST function to return the TIME expression with the smallest value.

Command> SELECT LEAST (TIME '13:59:59', TIME '13:59:58',
       > TIME '14:00:00') FROM dual;
< 13:59:58 >
1 row found.

LOWER and UPPER

The LOWER function converts expressions of type CHAR, NCHAR, VARCHAR2 or NVARCHAR2 to lowercase. The UPPER function converts expressions of type CHAR, NCHAR, VARCHAR2 or NVARCHAR2 to uppercase. Character semantics is supported for CHAR and VARCHAR2 types. The data type of the result is the same as the data type of the expression.

SQL syntax

{UPPER | LOWER} (Expression1)

Parameters

LOWER and UPPER have the following parameter:

Parameter Description
Expression1 An expression which is converted to lowercase (using LOWER) or uppercase (using UPPER).

Description

LOWER(?) and UPPER(?) are not supported, but you can combine it with the CAST operator. For example:

LOWER(CAST(? AS CHAR(30))) 

Command> SELECT LOWER (last_name) FROM employees WHERE employee_id = 100;
< king >
1 row found.

LPAD

The LPAD function returns Expression1, left-padded to length n characters with the sequence of characters in Expression2. This function is useful for formatting the output of a query.

SQL syntax

LPAD (Expression1, n [,Expression2])

Parameters

LPAD has the parameters:

Parameter Description
Expression1 CHAR, VARCHAR2, NCHAR or NVARCHAR2 operand or column to be left-padded. If Expression1 is longer than n, then LPAD returns the portion of Expression1 that fits in n.
n Length of characters returned by LPAD function. Must be a NUMBER integer or a value that can be implicitly converted to a NUMBER integer.
Expression2 Sequence of characters left-padded to Expression1. If you do not specify Expression2, then the default is a single blank. Operand or column can be of type CHAR, VARCHAR2, NCHAR, or NVARCHAR2.

Description

Examples

Use LPAD function to left-pad the string 'LPAD Function' with string 'DEMO-ONLY' plus 2 spaces. Replicate string DEMO-ONLY plus 2 spaces 3 times.

Command> SELECT LPAD ('LPAD Function', 46, 'DEMO-ONLY  ') FROM dual;
< DEMO-ONLY  DEMO-ONLY  DEMO-ONLY  LPAD Function >
1 row found.

Call LPAD function with length of -1. You see NULL is returned.

Command> SELECT LPAD ('abc', -1, 'a')  FROM dual;
< <NULL> >
1 row found.

LTRIM

The LTRIM function removes from the left end of Expression1 all of the characters contained in Expression2. TimesTen begins scanning Expression1 from its first character and removes all characters that appear in Expression2 until reaching a character not in Expression2 and returns the result.

SQL syntax

LTRIM (Expression1 [,Expression2])

Parameters

LTRIM has the parameters:

Parameter Description
Expression1 The CHAR, VARCHAR2, NCHAR or NVARCHAR2 operand or column to be trimmed. If Expression1 is a character literal, then enclose it in single quotes.
Expression2 Optional expression used for trimming Expression1. If Expression2 is a character literal, then enclose it in single quotes. If you do not specify Expression2, it defaults to a single blank. Operand or column can be of type CHAR,VARCHAR2, NCHAR, or NVARCHAR2.

Description

Examples

Call the LTRIM function to remove left-most 'x' and 'y' from string. LTRIM removes individual occurrences of 'x' and 'y', not pattern 'xy'.

Command> SELECT LTRIM ('xxxyyyxyxyLTRIM Example', 'xy') FROM dual;
< LTRIM Example >
1 row found.

Call the LTRIM function to remove YYYY-MM-DD from SYSDATE. Call TO_CHAR to convert SYSDATE to VARCHAR2.

Command> SELECT LTRIM (TO_CHAR(SYSDATE), '2007-08-21') FROM dual;
<  22:54:39 >
1 row found.

Call LTRIM to remove all characters from Expression1. In the first example, the data type is CHAR, so NULL is returned. In the second example, the data type is TT_CHAR, so the empty string is returned.

Command> CREATE TABLE ltrimtest (col1 CHAR (4), col2 TT_CHAR (4));
Command> INSERT INTO ltrimtest VALUES ('ABBB','ABBB');
1 row inserted.
Command> SELECT LTRIM (col1, 'AB') FROM ltrimtest;
< <NULL> >
1 row found.
Command> SELECT LTRIM (col2, 'AB') FROM ltrimtest;
<  >
1 row found.

MOD

Returns the remainder of an INTEGER expression divided by a second INTEGER expression.

SQL syntax

MOD(Expression1, Expression2)

Parameters

MOD has the following parameters:

Parameter Description
Expression1 An INTEGER expression.
Expression2 An INTEGER expression.

Description

The following example tests whether the value of the expression m is divisible by the value of expression n.

SELECT m, n FROM test WHERE MOD(m, n) = 0;

NCHR

The NCHR function returns the character having the specified Unicode value.

SQL syntax

NCHR(n)

Parameters

NCHR has the parameter:

Parameter Description
n The specified Unicode value. The character having this Unicode value is returned. The result is of type NVARCHAR2.

The following example returns the NCHAR character 187:

Command> SELECT NCHR(187) FROM dual;
< > >
1 row found.

Example

The following example returns the NCHAR character 187:

Command> SELECT NCHR(187) FROM dual;
< > >
1 row found.

NLSSORT

Returns the sort key value for the given string.

SQL syntax

NLSSORT (String [,'NLS_SORT = SortName'])

Parameters

NLSSORT has the following parameters:

Parameter Description
String Supported data types for String are CHAR, VARCHAR2, NCHAR and NVARCHAR2. Given the String, NLSSORT returns the sort key value used to sort the String.
['NLS_SORT = SortName'] SortName is either the linguistic sort sequence or BINARY. If you omit this parameter, then the default sort sequence for the session is used. Append to the SortName the suffix -ai for accent-insensitive sorting or -ci for case-insensitive sorting. For more information on acceptable linguistic SortName values, see "Supported linguistic sorts" in Oracle TimesTen In-Memory Database Reference.

Description

Examples

The following example illustrates sorting and comparison operations based on a linguistic sort sequence rather than on the binary value of the string. In addition, the example shows the same results can be obtained by using the ALTER SESSION... SET NLS_SORT statement.

Command> CREATE TABLE nsortdemo (name VARCHAR2 (15));
Command> INSERT INTO nsortdemo VALUES ('Gaardiner');
1 row inserted.
Command> INSERT INTO nsortdemo VALUES ('Gaberd');
1 row inserted.
Command> INSERT INTO nsortdemo VALUES ('Gaasten');
1 row inserted.
Command> # Perform Sort
Command> SELECT * FROM nsortdemo ORDER BY name;
< Gaardiner >
< Gaasten >
< Gaberd >
3 rows found.
Command> #Use function to perform sort
Command> SELECT * FROM nsortdemo ORDER BY NLSSORT (name, 'NLS_SORT = XDanish');
< Gaberd >
< Gaardiner >
< Gaasten >
3 rows found.
Command># comparison operation
Command> SELECT * FROM nsortdemo where Nnme > 'Gaberd';
0 rows found.
Command> #Use function in comparison operation
Command> SELECT * FROM nsortdemo WHERE NLSSORT (name, 'NLS_SORT = XDanish') >
> NLSSORT ('Gaberd', 'NLS_SORT = XDanish');
< Gaardiner >
< Gaasten >
2 rows found.
Command> #Use ALTER SESSION to obtain the same results
Command> ALTER SESSION SET NLS_SORT = 'XDanish';
Command> SELECT * FROM nsortdemo ORDER BY name;
< Gaberd >
< Gaardiner >
< Gaasten >
3 rows found.
Command> SELECT * FROM nsortdemo where name > 'Gaberd';
< Gaardiner >
< Gaasten >
2 rows found.

The following example creates a linguistic index:

Command> CREATE INDEX danishindex 
       > ON nsortdemo (NLSSORT (name, 'NLS_SORT =XDanish'));
Command> INDEXES N%;
Indexes on table USER1.NSORTDEMO:
  DANISHINDEX: non-unique T-tree index on columns:
    NLSSORT(NAME,'NLS_SORT = XDanish')
  1 index found.
1 table found.

NUMTODSINTERVAL

Converts a number or expression to an INTERVAL DAY TO SECOND type.

SQL syntax

NUMTODSINTERVAL (Expression1, IntervalUnit)

Parameters

NUMTODSINTERVAL has the parameters:

Parameter Description
Expression1 The argument can be any NUMBER value or an expression that can be implicitly converted to a NUMBER value.
IntervalUnit One of the string constants: 'DAY', 'HOUR', 'MINUTE', or 'SECOND'.

Examples

Example using NUMTODSINTERVAL with SYSDATE:

Command> SELECT SYSDATE + NUMTODSINTERVAL(20,'SECOND') FROM dual;
< 2007-01-28 09:11:06 >

NUMTOYMINTERVAL

Converts a number or expression to an INTERVAL YEAR TO MONTH type.

SQL syntax

NUMTOYMINTERVAL (Expression1, 'IntervalUnit')

Parameters

NUMTOYMINTERVAL has the parameters:

Parameter Description
Expression1 The argument can be any NUMBER value or an expression that can be implicitly converted to a NUMBER value.
IntervalUnit One of the string constants 'YEAR' or 'MONTH'.

Examples

An example using NUMTOYMINTERVAL:

Command>  SELECT SYSDATE + NUMTOYMINTERVAL(1,'MONTH') FROM dual;
< 2007-02-28 09:23:28 >
1 row found.

NVL

The NVL function replaces a null value with a second value.

SQL syntax

NVL(Expression1, Expression2)

Parameters

NVL has the parameters:

Parameter Description
Expression1 The expression whose values are to be tested for NULL.
Expression2 The alternate value to use if the value of Expression1 is NULL.

Description

Examples

This example checks for null values of commission_pct and replaces them with 'Not Applicable' for employees whose last name start with B.

Command> SELECT last_name, NVL(TO_CHAR(commission_pct), 'Not Applicable')
       > FROM employees 
       > WHERE last_name LIKE 'B%'
       > ORDER BY last_name;
< Baer, Not Applicable >
< Baida, Not Applicable >
< Banda, .1 >
< Bates, .15 >
< Bell, Not Applicable >
< Bernstein, .25 >
< Bissot, Not Applicable >
< Bloom, .2 >
< Bull, Not Applicable >
9 rows found.

POWER

The POWER function returns Base raised to the Exponent power. The base and exponent can be any numbers, but if the base is negative, the exponent must be an integer.

SQL syntax

POWER (Base, Exponent)

Parameters

POWER has the parameters:

Parameter Description
Base Operand or column can be any numeric type. POWER returns this value raised to Exponent power.
Exponent Operand or column can be any numeric type. If base is negative, exponent must be an integer.

Description

If either Base or Exponent is of type BINARY_FLOAT or BINARY_DOUBLE, the data type returned is BINARY_DOUBLE. If the Base is of type NUMBER or TT_DECIMAL, and the Exponent is not of type BINARY_FLOAT or BINARY_DOUBLE, the date type returned is NUMBER with maximum precision and scale. If Base is one of the TT* numeric types (TT_BIGINT, TT_INTEGER, TT_SMALLINT, or TT_TINYINT), the data type returned is BINARY_DOUBLE.

Example

Use the POWER function to return the commission_pct squared for the employee with employee_id equal to 145.

Command> SELECT employee_id,commission_pct FROM employees WHERE employee_id = 145;
< 145, .4 >
1 row found.

Command> SELECT POWER (commission_pct,2) FROM employees WHERE employee_id = 145;
< .16 >
1 row found.

ROUND (date)

Returns date rounded to the unit specified by the format model fmt. The value returned is of type DATE. If you do not specify fmt, then date is rounded to the nearest day.

SQL syntax

ROUND (date [,fmt]) 

Parameters

ROUND (date) has the parameters:

Parameter Description
date The date that is rounded. Must resolve to a date value.

If you do not specify fmt, then date is rounded to the nearest day.

[,fmt] The format model rounding unit. Specify either a constant or a parameter for fmt.

Description

Examples

Round date to the first day of the following year by specifying 'YEAR' as the format model:

Command> SELECT ROUND (DATE '2007-08-25','YEAR') FROM dual;
< 2008-01-01 00:00:00 >
1 row found.

Omit fmt. Specify date as type TIMESTAMP with a time of 13:00:00. date is rounded to nearest day:

Command> SELECT ROUND (TIMESTAMP '2007-08-16 13:00:00') FROM dual;
< 2007-08-17 00:00:00 >
1 row found.

ROUND (expression)

The ROUND function returns Expression1 rounded to Expression2 places to the right of the decimal point.

SQL syntax

ROUND (Expression1 [,Expression2])

Parameters

ROUND has the parameters:

Parameter Description
Expression1 Operand or column can be any numeric type.
Expression2 Operand or column that indicates how many places to round. Can be negative to round off digits left of the decimal point. If you omit Expression2, then Expression1 is rounded to 0 places. Must be an integer.

Description

Examples

Round a number 2 places to the right of the decimal point.

Command> SELECT ROUND (15.5555,2) FROM dual;
< 15.56 >
1 row found.

Round a number to the left of the decimal point by specifying a negative number for Expression2.

Command> SELECT ROUND (15.5555,-1) FROM dual;
< 20 >
1 row found.

Round a floating point number. Floating point numbers are rounded to nearest even value. Contrast this to rounding an expression of type NUMBER where the value is rounded up (for positive values).

Command> SELECT ROUND (1.5f), ROUND (2.5f) FROM dual;
< 2.00000000000000, 2.00000000000000 >
1 row found.
Command> SELECT ROUND (1.5), ROUND (2.5) FROM dual;
< 2, 3 >
1 row found.

RPAD

The RPAD function returns Expression1, right-padded to length n characters with Expression2, replicated as many times as necessary. This function is useful for formatting the output of a query.

SQL syntax

RPAD (Expression1, n [,Expression2])

Parameters

RPAD has the parameters:

Parameter Description
Expression1 CHAR, VARCHAR2, NCHAR or NVARCHAR2 operand or column to be right-padded. If Expression1 is longer than n, then RPAD returns the portion of Expression1 that fits in n.
n Length of characters returned by RPAD function. Must be a NUMBER integer or a value that can be implicitly converted to a NUMBER integer.
Expression2 CHAR, VARCHAR2, NCHAR or NVARCHAR2 operand or column to be right-padded to Expression1. If you do not specify Expression2, then the default is a single blank.

Description

Examples

Concatenate first_name and last_name from the employees table. Call the RPAD function to return first_name right-padded to length 12 with spaces and call RPAD a second time to return last_name right-padded to length 12 with spaces. Select first 5 rows.

Command> SELECT FIRST 5 CONCAT (RPAD (first_name,12),
       > RPAD (last_name,12)) FROM employees
       > ORDER BY first_name, last_name;
< Adam        Fripp        >
< Alana       Walsh        >
< Alberto     Errazuriz    >
< Alexander   Hunold       >
< Alexander   Khoo         >
5 rows found.

Call the RPAD function to return last_name right-padded to length 20 characters with the dot ('.') character. Use the employees table and select first 5 rows.

Command> SELECT FIRST 5 RPAD (last_name,20,'.') FROM employees
       > ORDER BY last_name;
< Abel................ >
< Ande................ >
< Atkinson............ >
< Austin.............. >
< Baer................ >
5 rows found.

RTRIM

The RTRIM function removes from the right end of Expression1 all of the characters contained in Expression2. TimesTen scans Expression1 backwards from its last character and removes all characters that appear in Expression2 until reaching a character not in Expression2 and then returns the result.

SQL syntax

RTRIM (Expression1 [,Expression2])

Parameters

RTRIM has the parameters:

Parameter Description
Expression1 The CHAR, VARCHAR2, NCHAR or NVARCHAR2 operand or column to be trimmed. If Expression1 is a character literal, then enclose it in quotes.
Expression2 Optional expression used for trimming Expression1. If Expression2 is a character literal, then enclose it in single quotes. If you do not specify Expression2, it defaults to a single blank. Operand or column can be of type CHAR,VARCHAR2, NCHAR, or NVARCHAR2.

Description

Examples

The following example trims the trailing spaces from col1 in table rtrimtest.

Command> CREATE TABLE rtrimtest (col1 VARCHAR2 (25));
Command> INSERT INTO rtrimtest VALUES ('abc     ');
1 row inserted.
Command> SELECT * FROM rtrimtest;
< abc      >
1 row found.
Command> SELECT RTRIM (col1) FROM rtrimtest;
< abc >
1 row found.

Call the RTRIM function to remove right-most 'x' and 'y' from string. RTRIM removes individual occurrences of 'x' and 'y', not pattern 'xy'.

Command> SELECT RTRIM ('RTRIM Examplexxxyyyxyxy', 'xy') FROM dual;
< RTRIM Example >
1 row found.

Call RTRIM to remove all characters from Expression1. In the first example, the data type is CHAR, so NULL is returned. In the second example, the data type is TT_CHAR, so the empty string is returned.

Command> CREATE TABLE rtrimtest (col1 CHAR (4), col2 TT_CHAR (4));
Command> INSERT INTO rtrimtest VALUES ('BBBA', 'BBBA');
1 row inserted.
Command> SELECT RTRIM (col1, 'AB') FROM rtrimtest;
< <NULL> >
1 row found.
Command> SELECT RTRIM (col2, 'AB') FROM rtrimtest;
<  >
1 row found.

SIGN

The SIGN function returns the sign of Expression.

SQL syntax

SIGN (Expression)

Parameters

SIGN has the parameter:

Parameter Description
Expression Operand or column can be any numeric data type.

Description

Examples

These examples illustrate use of the SIGN function with different data types. Table signex has been created and the columns have been defined with different data types. First, describe the table signex to see the data types of the columns. Then select each column to retrieve values for that column. Use the SIGN function to return the sign for the column.

Command> DESCRIBE signex;

Table SAMPLEUSER.SIGNEX:
  Columns:
    COL1                            TT_INTEGER
    COL2                            TT_BIGINT
    COL3                            BINARY_FLOAT
    COL4                            NUMBER (3,2)

1 table found.
(primary key columns are indicated with *)
Command> SELECT col1 FROM signex;
< 10 >
< -10 >
< 0 >
3 rows found.
Command> SELECT SIGN (col1) FROM signex;
< 1 >
< -1 >
< 0 >
3 rows found.
Command> SELECT col2 FROM signex;
< 0 >
< -3 >
< 0 >
3 rows found.
Command> SELECT SIGN (col2) from signex;
< 0 >
< -1 >
< 0 >
3 rows found.
Command> SELECT col3 FROM signex;
< 3.500000 >
< -3.560000 >
< NAN >
3 rows found.
Command> SELECT SIGN (col3) from signex;
< 1 >
< -1 >
< 1 >
3 rows found.
Command> SELECT col4 FROM signex;
< 2.2 >
< -2.2 >
< 0 >
3 rows found.
Command> SELECT SIGN (col4) from signex;
< 1 >
< -1 >
< 0 >
3 rows found.

SQRT

The SQRT function returns the square root of Expression.

SQL syntax

SQRT(Expression)

Parameters

SQRT has the parameter:

Parameter Description
Expression Operand or column can be any numeric data type.

Description

Examples

Use SQRT function to return the square root of the absolute value of -10. Then cast the value as BINARY_FLOAT.

Command> SELECT CAST (SQRT (ABS (-10)) AS BINARY_FLOAT ) FROM dual;
< 3.162278 >
1 row found.

String functions

TimesTen supports these string functions in SELECT statements:

A selected value that specifies a string function causes the SELECT result to be materialized. This causes overhead in both time and space.


SUBSTR, SUBSTRB, SUBSTR4

Returns a CHAR, VARCHAR2 or NVARCHAR2 that represents a substring of a CHAR or NCHAR string. The returned substring is of a specified number of characters, beginning from a designated starting point, relative to either the beginning or end of the string.

SQL syntax

{SUBSTR | SUBSTRB | SUBSTR4}=(char, m, n)

Parameters

SUBSTR has the parameters:

Parameter Description
char The string for which this function returns a substring. If char is a CHAR string, the result is a CHAR or VARCHAR2 string. If char is a NCHAR string, the result is a NVARCHAR2 string.
m The position at which to begin the substring. If m is positive, the first character of the returned string is m characters from the beginning of the string specified in char. Otherwise it is m characters from the end of the string. If ABS(m) is bigger than the length of the character string, a NULL value is returned.
n The number of characters to be included in the substring. If n is omitted, all characters to the end of the string specified in char are returned. If n is less than 1 or if char, m or n is NULL, NULL is returned.

Description

SUBSTR calculates lengths using characters as defined by character set. SUBSTRB uses bytes instead of characters. SUBSTR4 uses UCS4 code points.

Examples

In the first 5 rows of employees, select the first three characters of last_name:

SELECT FIRST 5 SUBSTR(last_name,1,3) FROM employees;
< Kin >
< Koc >
< De  >
< Hun >
< Ern >
5 rows found.

In the first 5 rows of employees, select the last five characters of last_name:

SELECT FIRST 5 SUBSTR(last_name,-5,5) FROM employees;
< <NULL> >
< chhar >
<  Haan >
< unold >
< Ernst >
5 rows found.

INSTR, INSTRB, INSTR4

Determines the first position, if any, at which one string occurs within another. If the substring does not occur in the string, then 0 is returned. The position returned is always relative to the beginning of CharExpr2. INSTR returns type NUMBER.

If you are using TimesTen type mode, for information on the INSTR function, see the Oracle TimesTen In-Memory Database Release 6.0.3 documentation.

SQL syntax

{INSTR | INSTRB | INSTR4} ( CharExpr2, CharExp1 [,m[,n]])

Parameters

INSTR has the parameters:

Parameter Description
CharExpr1 The substring to be found in string CharExpr2. If CharExpr1 does not occur in CharExpr2, then zero is returned. If either string is of length zero, NULL is returned.
CharExpr2 The string to be searched to find the position of CharExpr1.
m The optional position at which to begin the search. If m is specified as zero, the result is zero. If m is positive, the search begins at the CharExpr2+m. If m is negative, the search begins m characters from the end of CharExpr2.
n If n is specified it must be a positive value and the search returns the position of the nth occurrence of CharExpr1

Description

INSTR calculates strings using characters as defined by character set. INSTRB uses bytes instead of characters. INSTR4 uses UCS4 code points.

Examples

The following example uses INSTR to determine the position at which the substring 'ing' occurs in the string 'Washington':

Command> SELECT INSTR ('Washington', 'ing') FROM dual;
< 5 >
1 row found.

LENGTH, LENGTHB, LENGTH4

Returns the length of a given character string in an expression. LENGTH returns type NUMBER.

If you are using TimesTen type mode, for information on the LENGTH function, see the Oracle TimesTen In-Memory Database Release 6.0.3 documentation.

SQL syntax

{LENGTH|LENGTHB|LENGTH4} (CharExpr) 

Parameters

LENGTH has the parameter:

Parameter Description
CharExpr The string for which to return the length.

Description

The LENGTH functions return the length of CharExpr. LENGTH calculates the length using characters as defined by the character set. LENGTHB uses bytes rather than characters. LENGTH4 uses UCS4 code points.

Examples

Determine the length of the string 'William':

Command> SELECT LENGTH('William') FROM dual;
< 7 >
1 row found.

SYS_CONTEXT

Returns information about the current session.

The data type of the return value is VARCHAR2.

SQL syntax

SYS_CONTEXT('namespace', 'parameter' [, length ])

Parameters

SYS_CONTEXT has the parameters:

Parameter Description
namespace Value: USERENV

Other values result in a return of NULL.

parameter Supported values:
  • AUTHENTICATION_METHOD

  • CURRENT_USER

  • CURRENT_USERID

  • IDENTIFICATION_TYPE

  • LANG

  • LANGUAGE

  • NLS_SORT

  • SESSION_USER

  • SESSION_USERID

  • SID

length Number between 1 and 4000 bytes.

These are descriptions of the supported values for parameter:

Parameter Description
AUTHENTICATION_METHOD Returns the method of authentication for these types of users:
  • Local database user authenticated by password

  • External user authenticated by the operating system

CURRENT_USER The name of the database user whose privileges are currently active. This may change during the duration of a session to reflect the owner of any active definer's rights object. When no definer's rights object is active, CURRENT_USER returns the same value as SESSION_USER. When used directly in the body of a view definition, this returns the user that is executing the cursor that is using the view. It does not respect views used in the cursor as being definer's rights.
CURRENT_USERID The identifier of the database user whose privileges are currently active
IDENTIFICATION_TYPE Returns the way the user was created in the database. Specifically, it reflects the IDENTIFIED clause in the CREATE/ALTER USER syntax. In the list that follows, the syntax used during user creation is followed by the identification type returned:
  • IDENTIFIED BY password: LOCAL

  • IDENTIFIED EXTERNALLY: EXTERNAL

LANG The ISO abbreviation for the language name, a shorter form than the existing 'LANGUAGE' parameter.
LANGUAGE The language and territory currently used by the session, along with the database character set, in this form:

language_territory.characterset

NLS_SORT Binary or linguistic sort.
SESSION_USER The name of the database user at logon. This value remains the same throughout the duration of the session.
SESSION_USERID The identifier of the database user at logon.
SID The connection id of the current connection.

Description

The data type of the return value is VARCHAR2.

Examples

SELECT SYS_CONTEXT('USERENV', 'CURRENT_USER') FROM dual;
< TTUSER >
1 row found.

SELECT SYS_CONTEXT('USERENV', 'LANGUAGE') FROM dual;
< AMERICAN_AMERICA.AL32UTF8 >
1 row found.

SELECT SYS_CONTEXT('USERENV', 'IDENTIFICATION_TYPE') FROM dual;
< EXTERNAL >
1 row found.

SYSDATE and GETDATE

Returns the date in the format YYYY-MM-DD HH:MM:SS. The date represents the local current date and time, which is determined by the system on which the statement is executed.

If you are using TimesTen type mode, for information on SYSDATE, see the Oracle TimesTen In-Memory Database Release 6.0.3 documentation.

SQL syntax

SYSDATE | GETDATE( )

Parameters

The SYSDATE and GETDATE functions have no parameters.

Description

Examples

In this example, invoking SYSDATE returns the same date and time for all rows in the table:

Command> SELECT SYSDATE FROM dual;
< 2006-09-03 10:33:43 >
1 row found. 

This example invokes SYSDATE to insert the current data and time into column datecol:

Command> CREATE TABLE t (datecol DATE);
Command> INSERT INTO t VALUES (SYSDATE);
1 row inserted.
Command> SELECT * FROM t;
< 2006-09-03 10:35:50 >
1 row found.

In this example, GETDATE inserts the same date value for each new row in the table, even if the query takes several seconds.

INSERT INTO t1 SELECT GETDATE(), col1
   FROM t2 WHERE ...;

TO_CHAR is used with SYSDATE to return the date from table dual:

Command> SELECT TO_CHAR (SYSDATE) FROM dual;
< 2006-09-03 10:56:35 >
1 row found.

This example invokes TT_SYSDATE to return the TT_TIMESTAMP data type and then invokes ORA_SYSDATE to return the DATE data type:

Command> SELECT tt_sysdate FROM dual;
< 2006-10-31 20:02:19.440611 >
1 row found.
Command> SELECT ora_sysdate FROM dual;
< 2006-10-31 20:02:30 >
1 row found.

TO_CHAR

The TO_CHAR function converts a DATE, TIMESTAMP or numeric input value to a VARCHAR2.

If you are using TimesTen type mode, for information on the TO_CHAR function, see the Oracle TimesTen In-Memory Database Release 6.0.3 documentation.

SQL syntax

TO_CHAR ( Expression1[, Expression2 [, Expression3]])

Parameters

TO_CHAR has the parameters:

Parameter Description
Expression1 A DATE, TIMESTAMP or numeric expression.
Expression2 The format string. If omitted, TimesTen uses the default date format (YYYY-MM-DD).
Expression3 A CHAR or VARCHAR2 expression to specify the NLS parameter which is currently ignored.

Description

Examples

SELECT FIRST 5 first_name, 
               TO_CHAR (hire_date, 'MONTH DD, YYYY'),
               TO_CHAR (salary, '$999999.99')
  FROM employees;
< Steven, JUNE      17, 1987,   $24000.00 >
< Neena, SEPTEMBER 21, 1989,   $17000.00 >
< Lex, JANUARY   13, 1993,   $17000.00 >
< Alexander, JANUARY   03, 1990,    $9000.00 >
< Bruce, MAY       21, 1991,    $6000.00 >
5 rows found.

SELECT TO_CHAR(-0.12,'$B99.9999') FROM dual;
<   -$.1200 >
1 row found.

SELECT TO_CHAR(-12, 'B99999PR') FROM dual;
<     12  >
1 row found.

SELECT TO_CHAR(-12,'FM99999') FROM dual;
< -12 >
1 row found.

SELECT TO_CHAR(1234.1,'9,999.999') FROM dual;
<  1,234.100 >
1 row found.

TO_DATE

The TO_DATE function converts a CHAR or VARCHAR2 argument to a value of DATE data type

If you are using TimesTen type mode, for information on the TO_DATE function, see the Oracle TimesTen In-Memory Database Release 6.0.3 documentation.

SQL syntax

TO_DATE (Expression1[, Expression2 [, Expression3]])

Parameters

TO_DATE has the parameters:

Parameter Description
Expression1 A CHAR or VARCHAR2 expression.
Expression2 The format string. This expression is usually required. It is optional only when Expression1 is in the default date format YYYY-MM-DD HHMMSS.
Expression3 A CHAR or VARCHAR2 expression to specify the NLS parameter which is currently ignored.

Description

You can use a datetime format model with the TO_DATE function. For more information on datetime format models, see "Datetime format models".

Examples

Command> SELECT TO_DATE ('1999, JAN 14', 'YYYY, MON DD') FROM dual;
< 1999-01-14 00:00:00 >
1 row found.

Command> SELECT TO_CHAR(TO_DATE('1999-12:23','YYYY-MM:DD')) FROM dual;
< 1999-12-23 00:00:00 >
1 row found.

Command> SELECT TO_CHAR(TO_DATE('12-23-1997 10 AM:56:20', 
'MM-DD-YYYY HH AM:MI:SS'),'MONTH,DD YYYY HH:MI-SS') FROM dual;
< DECEMBER ,23 1997 10:56-20 >
1 row found.

TO_NUMBER

Converts an expression whose value is of type CHAR, VARCHAR2, NCHAR, NVARCHAR2, BINARY_FLOAT or BINARY_DOUBLE to a value of NUMBER type.

SQL syntax

TO_NUMBER (Expression[, format]) 

Parameters

TO_NUMBER has the parameters:

Parameter Description
Expression The expression to be converted.
format If specified, the format is used to convert Expression to a value of NUMBER type. The format consists of a format string that identifies the number format model. The format string can be either a constant or a parameter.

Description

You can use a number format model with the TO_NUMBER function. For more information on number format models, see "Number format models".

Examples

Command> SELECT TO_NUMBER ('100.00', '999D99') FROM dual;
< 100 >
1 row found.

Command> SELECT TO_NUMBER ('1210.73', '9999.99') FROM dual;
< 1210.73 >
1 row found.

TRIM

The TRIM function trims leading or trailing characters (or both) from a character string.

SQL syntax

There are four valid syntax options for TRIM:

Parameters

TRIM has the parameters:

Parameter Description
TRIM (

LEADING | TRAILING | BOTH

[Trim_character]

FROM

Expression )

LEADING | TRAILING| BOTH are qualifiers to TRIM function. LEADING removes all leading instances of Trim_character from Expression. TRAILING removes all trailing instances of Trim_character from Expression. BOTH removes leading and trailing instances of Trim_character from Expression.

Trim_character is optional. If specified, it represents the CHAR, VARCHAR2, NCHAR or NVARCHAR2 operand or column used for trimming Expression. Must be only one character. If you do not specify Trim_character, it defaults to a single blank. If Trim_character is a character literal, then enclose it in single quotes.

FROM is required.

Expression is the CHAR, VARCHAR2, NCHAR or NVARCHAR2 operand or column to be trimmed. If Expression is a character literal, then enclose it in single quotes.

TRIM (

Trim_character

FROM

Expression )

Removes both leading and trailing instances of Trim_character from Expression.

Trim_character is the CHAR, VARCHAR2, NCHAR or NVARCHAR2 operand or column used for trimming Expression. Must be only one character. If Trim_character is a character literal, then enclose it in single quotes.

FROM must follow Trim_character. Assumes LEADING | TRAILING | BOTH qualifiers have not been specified.

Expression is the CHAR, VARCHAR2, NCHAR or NVARCHAR2 operand or column to be trimmed. If Expression is a character literal, then enclose it in single quotes.

TRIM (

Expression )

If you specify Expression without a qualifier or Trim_character, then leading and trailing blank spaces are removed from Expression.

Expression is the CHAR, VARCHAR2, NCHAR or NVARCHAR2 operand or column to be trimmed. If Expression is a character literal, then enclose it in single quotes.


Description

Examples

Use TRIM function with qualifier to remove Trim_character '0' from Expression '0000TRIM Example0000':

Command> SELECT TRIM (LEADING '0' FROM '0000TRIM Example0000') FROM dual;
< TRIM Example0000 >
1 row found.
Command> SELECT TRIM (TRAILING '0' FROM '0000TRIM Example0000') FROM dual;
< 0000TRIM Example >
1 row found.
Command> SELECT TRIM (BOTH '0' FROM '0000TRIM Example0000') FROM dual;
< TRIM Example >
1 row found.

Use TRIM function with qualifier to remove blank spaces. Do not specify a Trim_character. Default value for Trim_character is blank space:

Command> SELECT TRIM (LEADING FROM '    TRIM Example    ') FROM dual;
< TRIM Example     >
1 row found.
Command> SELECT TRIM (TRAILING FROM '    TRIM Example    ') FROM dual;
<     TRIM Example >
1 row found.
Command> SELECT TRIM (BOTH FROM '    TRIM Example    ') FROM dual;
< TRIM Example >
1 row found.

Use TRIM function with Trim_character '0'. Do not specify a qualifier. Leading and trailing '0's are removed from Expression '0000TRIM Example0000':

Command> SELECT TRIM ('0' FROM '0000TRIM Example0000') FROM dual;
< TRIM Example >
1 row found.

Use TRIM function without a qualifier or Trim_character. Leading and trailing spaces are removed.

< TRIM Example >
1 row found.
Command> SELECT TRIM ('    TRIM Example    ') FROM dual;

TRUNC (date)

Returns date with the time portion of the day truncated to the unit specified by the format model fmt. The value returned is of type DATE. If you do not specify fmt, then date is truncated to the nearest day.

SQL syntax

TRUNC (date [,fmt]) 

Parameters

TRUNC (date) has the parameters:

Parameter Description
date The date that is truncated. Specify the DATE data type for date. The function returns data type DATE with the time portion of the day truncated to the unit specified by the format model. If you do not specify fmt, the date is truncated to the nearest day. An error is returned if you do not specify the DATE data type.
[,fmt] The format model truncating unit. Specify either a constant or a parameter for fmt.

Description

For the permitted format models to use in fmt, see "Format model for ROUND and TRUNC date functions".

Examples

Command> SELECT TRUNC (TO_DATE ('27-OCT-92','DD-MON-YY'),'YEAR') FROM dual;
< 2092-01-01 00:00:00 >
1 row found.

TRUNC (expression)

Returns a number truncated to a certain number of decimal places.

SQL syntax

TRUNC (Expression [,m]) 

Parameters

TRUNC has the parameters:

Parameter Description
Expression The Expression to truncate. Operands must be of type NUMBER. An error is returned if operands are not of type NUMBER. The value returned is of type NUMBER.
[,m] The number of decimal places to truncate to. If m is omitted, then the number is truncated to 0 places. The value of m can be negative to truncate (make zero) m digits left of the decimal point.

Examples

SELECT TRUNC (15.79,1) FROM dual;
< 15.7 >
1 row found.

SELECT TRUNC (15.79,-1) FROM dual;
< 10 >
1 row found.

TT_HASH

The TT_HASH function returns the hash value of an expression or list of expressions. This value is the value that is used by a hash index.

SQL syntax

TT_HASH(Expression [,...])

Parameters

TT_HASH has the parameter:

Parameter Description
Expression [,...] One or more expressions to be used to determine the hash value of the expression or list of expressions.

Description

Examples

The following query finds the set of rows whose primary key columns hash to a given hash value:

SELECT * FROM t1 
  WHERE TT_HASH(pkey_col1, pkey_col2, pkey_col3) = 12345678; 

UID

This function returns an integer (TT_INTEGER) that uniquely identifies the session user.

Examples

SELECT UID FROM dual;
< 10 >
1 row found.

UNISTR

The UNISTR function takes as its argument a string that resolves to data of type NVARCHAR2 and returns the value in UTF-16 format. Unicode escapes are supported. You can specify the Unicode encoding value of the characters in the string.

SQL syntax

UNISTR ('String')

Parameters

UNISTR has the parameter:

Parameter Description
'String' The string passed to the UNISTR function. The string resolves to type NVARCHAR2. TimesTen returns the value in UTF-16 format. You can specify Unicode escapes as part of the string.

Examples

The following example invokes the UNISTR function passing as an argument the string 'A\00E4a'. The value returned is the value of the string in UTF-16 format:

Command> SELECT UNISTR ('A\00E4a') FROM dual;
<Aäa> 1 row found.

USER functions

TimesTen supports these USER functions:

Each of these functions returns the name of the user that is currently connected to the TimesTen database.


CURRENT_USER

Returns the name of the TimesTen user currently connected to the database.

SQL syntax

CURRENT_USER

Parameters

CURRENT_USER has no parameters.

Examples

To return the name of the user who is currently connected to the database:

SELECT CURRENT_USER FROM dual;

USER

Returns the name of the TimesTen user who is currently connected to the database.

SQL syntax

USER

Parameters

USER has no parameters.

Examples

To return the name of the user who is currently connected to the database:

SELECT USER FROM dual;

SESSION_USER

Returns the name of the TimesTen user currently connected to the database.

SQL syntax

SESSION_USER

Parameters

SESSION_USER has no parameters.

Examples

To return the name of the session user:

SELECT SESSION_USER FROM dual;

SYSTEM_USER

Returns the name of the current database user as identified by the operating system.

SQL syntax

SYSTEM_USER

Parameters

SYSTEM_USER has no parameters.

Examples

To return the name of the operating system user:

SELECT SYSTEM_USER FROM dual;