Skip Headers
Oracle® TimesTen In-Memory Database SQL Reference
11g Release 2 (11.2.2)

E21642-07
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
PDF · Mobi · ePub

3 Expressions

Expressions are used for the following purposes:

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 Chapter 4, "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. 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.

Function A computed value. See Chapter 4, "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. 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

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

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

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

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 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'}

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.

This section covers the following format models:


Number format models

Use number format models in the following functions:

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:

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

The default american_america NLS language and territory setting is used.

A datetime format model is composed of one or more datetime format elements, which are shown in Table 3-2.

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 four-digit or two-digit input. If two-digit, provides the same return as RR. If you do not want this functionality, then enter the four-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

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

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

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 psuedocolumn

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