Expressions are used for the following purposes:
The select list of the INSERT...SELECT statement
A condition of the WHERE clause and the HAVING clause
The GROUP BY and ORDER BY clauses
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.
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
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.
{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. |
?
|
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
|
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". |
Arithmetic operators can be used between numeric values. See "Numeric data types".
Arithmetic operators can also be used between datetime values and interval types. The result of a datetime expression is either a datetime data type or an interval data type.
Arithmetic operators cannot be applied to string values.
Elements in an expression are evaluated in the following order:
Aggregate functions and expressions in parentheses
Unary pluses and minuses
The * and / operations
The + and – operations
Elements of equal precedence are evaluated in left-to-right order
You can enclose expressions in parentheses to control the order of their evaluation. For example:
10 * 2 – 1 = 19 but 10 * (2 – 1) = 10
Type conversion, truncation, underflow, or overflow can occur when some expressions are evaluated. See Chapter 1, "Data Types".
If either operand in a numeric expression is NULL, the result is NULL.
Since NVL takes two parameters, both designated as an "expression", TimesTen does not permit NULL in either position. If there is a NULL value in an expression, comparison operators and other predicates evaluate to NULL. See Chapter 4, "Search Conditions" for more information on evaluation of comparison operators and predicates containing NULL values. TimesTen permits inserting NULL, but in general INSERT takes only specific values, and not general expressions.
The query optimizer and execution engine permit multiple rowid lookups when a predicate specifies a disjunct of rowid equalities or uses IN. For example, multiple fast rowid lookups are executed for:
WHERE ROWID = :v1 OR ROWID = :v2
or equivalently:
WHERE ROWID IN (:v1, :v2)
The ? or :DynamicParameter can be used as a dynamic parameter in an expression.
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.
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.
[NOT] EXISTS | [NOT] IN (Subquery) Expression {= | <> | > | >= | < | <= } [ANY | ALL] (Subquery) Expression [NOT] IN (ValueList | Subquery)
TimesTen supports queries with the characteristics listed in each section.
A subquery can appear in the WHERE clause or HAVING clause of any statement except one that creates a materialized view. Only one table subquery can be specified in a predicate. These predicates can be specified in a WHERE or HAVING clause, an OR expression within a WHERE or HAVING clause, or an ON clause of a joined table. They cannot be specified in a CASE expression, a materialized view, or a HAVING clause that uses the + operator for outer joins.
A subquery can be specified in an EXISTS or NOT EXISTS predicate, a quantified predicate with ANY or ALL, or a comparison predicate. The allowed operators for both comparison and quantified predicates are: =, <, >, <=, >=, <>. The subquery cannot be connected to the outer query through a UNIQUE or NOT UNIQUE operator.
Only one subquery can be specified in a quantified or comparison predicate. Specify the subquery as either the right operand or the left operand of the predicate, but not both.
FIRST NumRows is not supported in subquery statements.
In a query specified in a quantified or comparison predicate, the underlying SELECT must have a single expression in the select list. In a query specified in a comparison predicate, if the underlying select returns a single row, the return value is the select result. If the underlying select returns no row, the return value is NULL. It is an error if the subquery returns multiple rows.
A scalar subquery returns a single value.
A nonverifiable scalar subquery has a predicate such that the optimizer cannot detect at compile time that the subquery returns at most one row for each row of the outer query. The subquery cannot be specified in an OR expression.
Neither outer query nor any scalar subquery should have a DISTINCT modifier.
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 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.
{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
|
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. |
If an aggregate function is computed over an empty table in which GROUP BY is not used, the results are as follows:
COUNT returns 0.
AVG, SUM, MAX, and MIN return NULL.
If an aggregate function is computed over an empty group or an empty grouped table (GROUP BY is used):
COUNT returns nothing.
AVG, SUM, MAX, and MIN return nothing.
For SUM:
If the source is TT_TINYINT, TT_SMALLINT, or TT_INTEGER, the result data type is TT_INTEGER.
If the source is NUMBER, then the result data type is NUMBER with undefined scale and precision.
If the source is TT_DECIMAL, then the result data type is TT_DECIMAL with maximum precision.
For all other data types, the result data type is the same as the source.
For MAX and MIN:
The result data type is the same as the source.
For AVG:
AVG is evaluated as SUM/COUNT. The result data type is derived using the rule that is applied for the DIV operator.
See Chapter 1, "Data Types" for information about:
Truncation and type conversion that may occur during the evaluation of aggregate functions.
Precision and scale of aggregate functions involving numeric arguments.
Control of the result type of an aggregate function.
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
A constant is a literal value.
{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
}
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.
Use number format models in the following functions:
In the TO_CHAR function to translate a value of NUMBER, BINARY_FLOAT, or BINARY_DOUBLE data type to VARCHAR2 data type.
In the TO_NUMBER function to translate a value of CHAR or VARCHAR2 data type to NUMBER data type.
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 |
|---|---|---|
|
|
9,999 |
Returns a comma in the specified position. You can specify multiple commas in a number format model. Restrictions:
|
|
|
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. |
|
|
0999 9990 |
Returns leading zeros. Returns trailing zeros. |
|
|
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. |
|
|
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). |
|
|
C999 |
Returns in the specified position the ISO currency symbol. |
|
|
|
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. |
|
|
|
Returns a value in scientific notation. |
|
|
|
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. |
|
|
|
Returns the local currency symbol in the specified position. |
|
|
|
Returns negative value with a trailing minus sign ( Returns positive value with a trailing blank. Restriction: The |
|
|
|
Returns negative value in angle brackets ( Returns positive value with a leading and trailing blank. Restriction: The |
|
|
|
Returns a value as Roman numerals in uppercase. |
|
|
|
Returns a value as Roman numerals in lowercase. Value can be an integer between 1 and 3999. |
|
|
|
Returns negative value with a leading minus sign ( Returns positive value with a leading plus sign ( |
|
|
|
Returns negative value with a trailing minus sign ( Returns positive value with a trailing plus sign ( Restriction: The |
|
|
|
The text minimum number format model returns (in decimal output) the smallest number of characters possible. This element is case insensitive. The default is Restrictions:
|
|
|
|
Returns the euro or other dual currency symbol in the specified position. |
|
|
|
Returns a value multiplied by 10n (and if necessary, rounds it up), where |
|
|
|
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:
|
Use datetime format models in the following functions:
In the TO_CHAR or TO_DATE functions to translate a character value that is in a format other than the default format for a datetime value.
In the TO_CHAR function to translate a datetime value that is in a format other than the default format into a string.
The total length of a datetime format model cannot exceed 22 characters.
The default american_america NLS language and territory setting is used.
A datetime format model is composed of one or more datetime format elements.
Table 3-2 Datetime format elements
| Element | Description |
|---|---|
|
- |
Punctuation and quoted text is reproduced in the result. |
|
|
AD indicator with or without periods. |
|
|
Meridian indicator with or without periods. |
|
|
BC indicator with or without periods. |
|
|
Day of week (1-7). |
|
|
Name of day, padded with blanks to display width of widest name of day. |
|
|
Day of month (1-31). |
|
|
Day of year. |
|
|
Returns a value in the long date format. In the default Restriction: Specify this format only with the |
|
|
Returns a value in the short date format. In the default Restriction: Specify this format only with the |
|
|
Abbreviated name of day. |
|
|
Returns a value with no leading or trailing blanks. |
|
|
Requires exact matching between the character data and the format model. |
|
|
Hour of day (1-12). |
|
|
Hour of day (0-23). |
|
|
Julian day: The number of days since January 1, 4712 BC. Numbers specified with |
|
|
Minute (0-59). |
|
|
Month (01-12. January = 01). |
|
|
Abbreviated name of month. |
|
|
Name of month padded with blanks to display width of the widest name of month. |
|
|
Roman numeral month (I-XII. January = I). |
|
|
Stores 20th century dates in the 21st century using only two digits. |
|
|
Rounds year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as |
|
|
Second (0-59). |
|
|
Seconds past midnight (0-86399). |
|
|
Returns a value in the short time format. Restriction: Specify this format only with the |
|
|
Local radix character. Example: |
|
|
Year with comma in this position. |
|
|
4-digit year. |
|
|
Last 3, 2, or 1 digit (s) of year. |
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
|
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
|
Year. All year output rounds up on July 1 |
IYYY
|
ISO year |
Q |
Quarter (rounds up on the sixteenth day of the second month of the quarter) |
MONTH
|
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
|
Day of year |
DAY
|
Starting day of the week |
HH
|
Hour |
MI |
Minute |
Use this format model when invoking the TO_CHAR function to convert a datetime value of TT_TIMESTAMP or TT_DATE. In addition, use this format model when invoking the TO_CHAR function to convert any numeric value other than NUMBER or ORA_FLOAT.
If a numeric value does not fit in the specified format, TimesTen truncates the value.
The format string cannot exceed 50 characters.
D always results in a decimal point. Its value cannot be changed with an NLS parameter.
If a float with an absolute value less than 1e-126 or greater than 1e126 is specified as input to the TO_CHAR function, TimesTen returns an error.
| Format | Description |
|---|---|
DD |
Day of month (1-31) |
MM |
Month (1-12) |
MON |
Month (three character prefix) |
MONTH |
Month (full name blank-padded to 9 characters) |
YYYY |
Year (four digits) |
Y,YYY |
Year (with comma as shown) |
YYY |
Year (last three digits) |
YY |
Year (last two digits) |
Y |
Year (last digit) |
Q |
Quarter |
HH |
Hour (1-12) |
HH12 |
Hour (1-12) |
HH24 |
Hour (0-23) |
MI |
Minute (0-59) |
SS |
Second (0-59) |
FF |
Fractions of a second to a precision of 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. |
The ABS function returns the absolute value of Expression.
ABS(Expression)
ABS has the parameter:
| Parameter | Description |
|---|---|
Expression |
Operand or column can be any numeric data type. Absolute value of Expression is returned. |
If Expression is of type TT_DECIMAL or NUMBER, the data type returned is NUMBER with maximum precision and scale. Otherwise, ABS returns the same data type as the numeric data type of Expression.
If the value of Expression is NULL, NULL is returned. If the value of the Expression is -INF, INF is returned.
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.
The ADD_MONTHS function returns the date resulting from date plus integer months.
ADD_MONTHS(date,integer)
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. |
The return type is always DATE regardless of the data type of date. Supported data types are DATE and TIMESTAMP.
Data types TT_DATE and TT_TIMESTAMP are not supported.
If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month. Otherwise, the result has the same day component as date.
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.
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.
ASCIISTR ([N]'String')
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. |
The ASCIISTR function allows you to see the representation of a string value that is not in the database character set.
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.
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.
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
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. |
CASE expression cannot be specified in the value clause of an INSERT statement.
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;
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:
Numeric value to numeric or BCD (Binary Coded Decimal)
NCHAR to NCHAR
CHAR string to BINARY string or DATE, TIME or TIMESTAMP
BINARY string to BINARY or CHAR string
DATE, TIME or TIMESTAMP to CHAR
CAST
( {Expression | NULL} AS DataType )
CAST has the parameters:
| Parameter | Description |
|---|---|
Expression |
Specifies the value to be converted. |
AS DataType |
Specifies the resulting data type. |
CAST to a domain name is not supported.
Casting a selected value may cause the SELECT statement to take more time and memory than a SELECT statement without a CAST expression.
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);
The CHR function returns the character having the specified binary value in the database character set.
CHR(n)
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. |
For single-byte character sets, if n >256, then TimesTen returns the binary value of n mod 256.
For multibyte character sets, n must resolve to one code point. Invalid code points are not validated. If you specify an invalid code point, the result is indeterminate.
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.
The CEIL function returns the smallest integer greater than or equal to Expression.
CEIL(Expression)
CEIL has the parameter:
| Parameter | Description |
|---|---|
Expression |
Operand or column can be any numeric data type. |
If Expression is of type TT_DECIMAL or NUMBER, the data type returned is NUMBER with maximum precision and scale. Otherwise, CEIL returns the same data type as the numeric data type of Expression.
If the value of Expression is NULL, NULL is returned. If the value of Expression is -INF, INF, or NaN, the value returned is -INF, INF, or NaN respectively.
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.
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.
COALESCE(Expression1, Expression2 [,...])
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. |
This function is a generalization of the NVL function.
Use COALESCE as a variation of the CASE expression. For example:
COALESCE (Expression1, Expression2)
is equivalent to:
CASE WHEN Expression1 IS NOT NULL THEN Expression1 ELSE Expression2 END
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.
The CONCAT function concatenates one character string with another to form a new character string.
CONCAT(Expression1, Expression2)
CONCAT has the parameters:
| Parameter | Description |
|---|---|
Expression1 |
A CHAR, VARCHAR2, NCHAR or NVARCHAR2 expression. |
Expression2 |
A CHAR, VARCHAR2, NCHAR or NVARCHAR2 expression. |
CONCAT returns Expression1 concatenated with Expression2.
The type of Expression1 and Expression2 must be compatible.
If Expression2 is NULL, CONCAT returns Expression1. If Expression1 is NULL, CONCAT returns Expression2.
If both Expression1 and Expression2 are NULL, CONCAT returns NULL.
The return type of CONCAT depends on the types of Expression1 and Expression2. The following table summarizes how the return type is determined.
| Expression1 | Expression2 | CONCAT |
|---|---|---|
CHAR(m) |
CHAR(n) |
CHAR(m+n) |
CHAR(m) |
VARCHAR2(n) |
VARCHAR2(m+n) |
VARCHAR2(m) |
CHAR(n) |
VARCHAR2(m+n) |
VARCHAR2(m) |
VARCHAR2(n) |
VARCHAR2(m+n) |
The treatment of NCHAR and NVARCHAR2 is similar. If one of the operands is of varying length, then the result is of varying length. Otherwise the result is of a fixed length.
The concatenation of CHAR, NCHAR, VARCHAR2, and NVARCHAR2 types are supported. The result type of character types concatenated with ncharacter types is ncharacter types.
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".
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.
DECODE(Expression, {SearchValue, Result [,...])} [,Default])
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. |
If an expression is NULL, then the NULL expression equals a NULL search value.
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.
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.
EXTRACT (DateTimeField FROM IntervalExpression | DateTimeExpression)
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. |
Some combinations of DateTime field and DateTime or interval value expression result in ambiguity. In these cases, TimesTen returns UNKNOWN.
The field you are extracting must be a field of the IntervalExpression or DateTimeExpression. For example, you can extract only YEAR, MONTH, and DAY from a DATE value. Likewise, you can extract HOUR, MINUTE or SECOND only from the TIME, DATE, or TIMESTAMP data type.
The fields are extracted into a NUMBER value.
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.
The FLOOR function returns the largest integer equal to or less than Expression.
FLOOR (Expression)
FLOOR has the parameter:
| Parameter | Description |
|---|---|
Expression |
Operand or column can be any numeric data type. |
If Expression is of type TT_DECIMAL or NUMBER, the data type returned is NUMBER with maximum precision and scale. Otherwise, FLOOR returns the same data type as the numeric data type of Expression.
If the value of Expression is NULL, NULL is returned. If the value of Expression is -INF, INF, or NaN, the value returned is -INF, INF, or NaN respectively.
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.
The GREATEST function returns the greatest of the list of one or more expressions.
GREATEST (Expression [,...])
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. |
Each expression in the list must be from the same data type family or date subfamily. Data type families include numeric, character and date. The date family includes four subfamilies: date family, TIME family, TT_DATE family, and TT_TIMESTAMP family. As an example, do not specify a numeric expression and a character expression in the list of expressions. Similarly, do not specify a date expression and a TT_TIMESTAMP expression in the list of expressions.
If the first Expression is numeric, then TimesTen determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type before the comparison, and returns that data type.
If the first Expression is in the character family, and the operand or column is of type CHAR or VARCHAR2, the data type returned is VARCHAR2. If the operand or column is of type NCHAR or NVARCHAR2, the data type returned is NVARCHAR2. The returned data type length is equal to the length of the largest expression. If one operand or column is of type CHAR or VARCHAR2 and the second operand or column is of type NCHAR or NVARCHAR2, the data type returned is NVARCHAR2.
TimesTen uses nonpadded comparison semantics for data types from the character family.
If the first expression is in the date family, the data type returned is the same data type as the first expression.
If any of the expressions is NULL, the result is NULL.
If the first Expression is in the character family, and the operand or column is of type TT_CHAR or TT_VARCHAR, the data type returned is TT_VARCHAR. If the operand or column is of type TT_NCHAR or TT_NVARCHAR, the data type returned is TT_NVARCHAR. The returned data type length is equal to the largest of the expressions.
You can specify a maximum of 256 expressions.
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.
The LEAST function returns the smallest of the list of one or more expressions.
LEAST (Expression [,...])
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. |
Each expression in the list must be from the same data type family or date subfamily. Data type families include numeric, character and date. The date family includes four subfamilies: date family, TIME family, TT_DATE family, and TT_TIMESTAMP family. As an example, do not specify a numeric expression and a character expression in the list of expressions. Similarly, do not specify a date expression and a TT_TIMESTAMP expression in the list of expressions.
If the first Expression is numeric, then TimesTen determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type before the comparison, and returns that data type.
If the first Expression is in the character family, and the operand or column is of type CHAR or VARCHAR2, the data type returned is VARCHAR2. If the operand or column is of type NCHAR or NVARCHAR2, the data type returned is NVARCHAR2. The returned data type length is equal to the length of the largest expression. If one operand or column is of type CHAR or VARCHAR2 and the second operand or column is of type NCHAR or NVARCHAR2, the data type returned is NVARCHAR2.
TimesTen uses nonpadded comparison semantics for data types from the character family.
If the first expression is in the date family, the data type returned is the same data type as the first expression.
If any of the expressions is NULL, the result is NULL.
If the first Expression is in the character family, and the operand or column is of type TT_CHAR or TT_VARCHAR, the data type returned is TT_VARCHAR. If the operand or column is of type TT_NCHAR or TT_NVARCHAR, the data type returned is TT_NVARCHAR. The returned data type length is equal to the largest of the expressions.
You can specify a maximum of 256 expressions.
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.
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.
{UPPER | LOWER} (Expression1)
LOWER and UPPER have the following parameter:
| Parameter | Description |
|---|---|
Expression1 |
An expression which is converted to lowercase (using LOWER) or uppercase (using UPPER). |
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.
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.
LPAD (Expression1, n [,Expression2])
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. |
If Expression1 is of type CHAR or VARCHAR2, the data type returned is VARCHAR2. If Expression1 is of type NCHAR or NVARCHAR2, the data type returned is NVARCHAR2.
The returned data type length is equal to n if n is a constant. Otherwise, the maximum result length of 8300 is returned.
You can specify TT_CHAR, TT_VARCHAR, TT_NCHAR, and TT_NVARCHAR for Expression1 and Expression2. If Expression1 is of type TT_CHAR or TT_VARCHAR, the data type returned is TT_VARCHAR. If Expression1 is of type TT_NCHAR or TT_NVARCHAR, the data type returned is TT_NVARCHAR.
For CHAR, VARCHAR2, NCHAR, and NVARCHAR2 types:
If either Expression1 or Expression2 is NULL, the result is NULL. If n is less than or equal to 0, then the result is NULL.
For TT_CHAR, TT_VARCHAR, TT_NCHAR and TT_NVARCHAR types:
If either Expression1 or Expression2 is not NULL and if n is less than or equal to 0, then the result is the empty string.
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.
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.
LTRIM (Expression1 [,Expression2])
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. |
If Expression1 is of type CHAR or VARCHAR2, the data type returned is VARCHAR2. If Expression1 is of type NCHAR or NVARCHAR2, the data type returned is NVARCHAR2. The returned data type length is equal to the data type length of Expression1.
If Expression1 is a data type defined with CHAR length semantics, the returned length is expressed in CHAR length semantics.
If either Expression1 or Expression2 is NULL, the result is NULL.
You can specify TT_CHAR, TT_VARCHAR, TT_NCHAR, and TT_NVARCHAR for Expression1 and Expression2. If Expression1 is of type TT_CHAR or TT_VARCHAR, the data type returned is TT_VARCHAR. If Expression1 is of type TT_NCHAR or TT_NVARCHAR, the data type returned is TT_NVARCHAR.
If Expression1 is of type CHAR or VARCHAR2 and Expression2 is of type NCHAR or NVARCHAR2, then Expression2 is demoted to CHAR or VARCHAR2 before LTRIM is invoked. The conversion of Expression2 could be lost. If the trim character of Expression2 is not in the database character set, then the query may produce unexpected results.
For CHAR, VARCHAR2, NCHAR, and NVARCHAR2 types:
If all the characters in Expression1 are removed by the LTRIM function, then the result is NULL.
For TT_CHAR, TT_VARCHAR, TT_NCHAR and TT_NVARCHAR types:
If all the characters in Expression1 are removed by the LTRIM function, then the result is the empty string.
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.
Returns the remainder of an INTEGER expression divided by a second INTEGER expression.
MOD(Expression1, Expression2)
MOD has the following parameters:
| Parameter | Description |
|---|---|
Expression1 |
An INTEGER expression. |
Expression2 |
An INTEGER expression. |
MOD returns the remainder of Expression1 divided by Expression2.
If Expression2 is 0, then MOD returns Expression1.
If either Expression1 or Expression2 is NULL, MOD returns NULL.
MOD is treated as a binary arithmetic operation, so the return type is determined according to the rules specified in Chapter 1, "Data Types".
The MOD function behaves differently from the classic mathematical modulus function when one of the operands is negative. The following table illustrates this difference:
| M | N | Classic Modulus | MOD(M,N) |
|---|---|---|---|
11 |
3 |
2 |
2 |
11 |
-3 |
-1 |
2 |
-11 |
3 |
1 |
-2 |
-11 |
-3 |
-2 |
-2 |
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;
The NCHR function returns the character having the specified Unicode value.
NCHR(n)
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.
The following example returns the NCHAR character 187:
Command> SELECT NCHR(187) FROM dual; < > > 1 row found.
Returns the sort key value for the given string.
NLSSORT (String [,'NLS_SORT = SortName'])
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. |
The returned sort key value is of type VARBINARY.
You can create a linguistic index for linguistic comparisons.
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.
Converts a number or expression to an INTERVAL DAY TO SECOND type.
NUMTODSINTERVAL (Expression1, IntervalUnit)
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'. |
Example using NUMTODSINTERVAL with SYSDATE:
Command> SELECT SYSDATE + NUMTODSINTERVAL(20,'SECOND') FROM dual; < 2007-01-28 09:11:06 >
Converts a number or expression to an INTERVAL YEAR TO MONTH type.
NUMTOYMINTERVAL (Expression1, 'IntervalUnit')
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'. |
An example using NUMTOYMINTERVAL:
Command> SELECT SYSDATE + NUMTOYMINTERVAL(1,'MONTH') FROM dual; < 2007-02-28 09:23:28 > 1 row found.
The NVL function replaces a null value with a second value.
NVL(Expression1, Expression2)
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. |
The data types of Expression1 and Expression2 must be compatible.
If Expression1 is NULL, the NVL function returns Expression2. If Expression1 is NOT NULL, the NVL function returns Expression1.
The NVL function can be used in the WHERE or HAVING clause of SELECT, UPDATE, or DELETE statements and in the SELECT list of a SELECT statement.
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.
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.
POWER (Base, Exponent)
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. |
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.
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.
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.
ROUND (date [,fmt])
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] |
The format model rounding unit. Specify either a constant or a parameter for fmt. |
Date can be of type DATE or TIMESTAMP. The data type returned is DATE.
Data types TT_DATE and TT_TIMESTAMP are not supported.
For the supported format models to use in fmt, see "Format model for ROUND and TRUNC date functions".
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.
The ROUND function returns Expression1 rounded to Expression2 places to the right of the decimal point.
ROUND (Expression1 [,Expression2])
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. |
If you omit Expression2, and Expression1 is of type TT_DECIMAL, the data type returned is NUMBER with maximum precision and scale. Otherwise, if you omit Expression2, the data type returned is the same as the numeric data type of Expression1.
If you specify Expression2, the data type returned is NUMBER with maximum precision and scale.
If Expression1 is of type BINARY_FLOAT or BINARY_DOUBLE, the value of Expression1 is rounded to the nearest even value. Otherwise, the value of Expression1 is rounded away from 0 (for example, to x+1 when x.5 is positive and to x-1 when x.5 is negative).
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.
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.
RPAD (Expression1, n [,Expression2])
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. |
If Expression1 is of type CHAR or VARCHAR2, the data type returned is VARCHAR2. If Expression1 is of type NCHAR or NVARCHAR2, the data type returned is NVARCHAR2.
The returned data type length is equal to n if n is a constant. Otherwise, the maximum result length of 8300 is returned.
You can specify TT_CHAR, TT_VARCHAR, TT_NCHAR, and TT_NVARCHAR for Expression1 and Expression2. If Expression1 is of type TT_CHAR or TT_VARCHAR, the data type returned is TT_VARCHAR. If Expression1 is of type TT_NCHAR or TT_NVARCHAR, the data type returned is TT_NVARCHAR.
For CHAR, VARCHAR2, NCHAR, and NVARCHAR2 types:
If either Expression1 or Expression2 is NULL, the result is NULL. If n is less than or equal to 0, then the result is NULL.
For TT_CHAR, TT_VARCHAR, TT_NCHAR and TT_NVARCHAR types:
If either Expression1 or Expression2 is not NULL and if n is less than or equal to 0, then the result is the empty string.
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.
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.
RTRIM (Expression1 [,Expression2])
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. |
If Expression1 is of type CHAR or VARCHAR2, the data type returned is VARCHAR2. If Expression1 is of type NCHAR or NVARCHAR2, the data type returned is NVARCHAR2. The returned data type length is equal to the data type length of Expression1.
If Expression1 is a data type defined with CHAR length semantics, the returned length is expressed in CHAR length semantics.
If either Expression1 or Expression2 is NULL, the result is NULL.
You can specify TT_CHAR, TT_VARCHAR, TT_NCHAR, and TT_NVARCHAR for Expression1 and Expression2. If Expression1 is of type TT_CHAR or TT_VARCHAR, the data type returned is TT_VARCHAR. If Expression1 is of type TT_NCHAR or TT_NVARCHAR, the data type returned is TT_NVARCHAR.
If Expression1 is of type CHAR or VARCHAR2 and Expression2 is of type NCHAR or NVARCHAR2, then Expression2 is demoted to CHAR or VARCHAR2 before RTRIM is invoked. The conversion of Expression2 could be lost. If the trim character of Expression2 is not in the database character set, then the query may produce unexpected results.
For CHAR, VARCHAR2, NCHAR, and NVARCHAR2 types:
If all the characters in Expression1 are removed by the RTRIM function, then the result is NULL.
For TT_CHAR, TT_VARCHAR, TT_NCHAR and TT_NVARCHAR types:
If all the characters in Expression1 are removed by the RTRIM function, then the result is the empty string.
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.
The SIGN function returns the sign of Expression.
SIGN (Expression)
SIGN has the parameter:
| Parameter | Description |
|---|---|
Expression |
Operand or column can be any numeric data type. |
If Expression is of type NUMBER or TT_DECIMAL, the data type returned is NUMBER with maximum precision and scale. Otherwise, the data type returned is TT_INTEGER.
For numeric types that are not binary floating-point numbers, the sign is:
-1 if the value of Expression is <0
0 if the value of Expression is = 0
1 if the value of Expression is > 0
For binary floating-point numbers (BINARY_FLOAT and BINARY_DOUBLE), this function returns the sign bit of the number. The sign bit is:
-1 if the value of Expression is <0
+1 if the value of Expression is >= 0 or the value of Expression is equal to NaN.
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.
The SQRT function returns the square root of Expression.
SQRT(Expression)
SQRT has the parameter:
| Parameter | Description |
|---|---|
Expression |
Operand or column can be any numeric data type. |
If Expression is of type NUMBER or TT_DECIMAL, the data type returned is NUMBER with maximum precision and scale. If Expression is of type BINARY_FLOAT, the data type returned is BINARY_FLOAT. Otherwise, the data type returned is BINARY_DOUBLE.
If Expression is of type NUMBER or TT_DECIMAL, the value of Expression cannot be negative.
If Expression resolves to a binary floating-point number (BINARY_FLOAT or BINARY_DOUBLE):
If the value of the Expression is > = 0, the result is positive.
If the value of the Expression is = -0, the result is -0.
If the value of the Expression is < 0, the result is NaN.
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.
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.
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.
{SUBSTR | SUBSTRB | SUBSTR4}=(char, m, n)
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. |
SUBSTR calculates lengths using characters as defined by character set. SUBSTRB uses bytes instead of characters. SUBSTR4 uses UCS4 code points.
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.
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.
{INSTR | INSTRB | INSTR4} ( CharExpr2, CharExp1 [,m[,n]])
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 |
INSTR calculates strings using characters as defined by character set. INSTRB uses bytes instead of characters. INSTR4 uses UCS4 code points.
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.
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.
{LENGTH|LENGTHB|LENGTH4} (CharExpr)
LENGTH has the parameter:
| Parameter | Description |
|---|---|
CharExpr |
The string for which to return the length. |
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.
Determine the length of the string 'William':
Command> SELECT LENGTH('William') FROM dual;
< 7 >
1 row found.
Returns information about the current session.
The data type of the return value is VARCHAR2.
SYS_CONTEXT('namespace', 'parameter' [, length ])
SYS_CONTEXT has the parameters:
| Parameter | Description |
|---|---|
namespace |
Value: USERENV
Other values result in a return of |
parameter |
Supported values:
|
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:
|
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:
|
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:
|
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. |
The data type of the return value is VARCHAR2.
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.
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.
SYSDATE | GETDATE( )
The SYSDATE and GETDATE functions have no parameters.
SYSDATE and GETDATE perform identically. SYSDATE is compatible with Oracle syntax. GETDATE is compatible with Microsoft SQL Server syntax.
SYSDATE and GETDATE have no arguments, and return a DATE value.
The SYSDATE or GETDATE value is only retrieved during execution.
Any required changes to the date (to incorporate a different time zone or Daylight Savings Time, for example) must occur at the system level. The date cannot be altered using SYSDATE or GETDATE.
The SYSDATE and GETDATE functions return the DATE data type. The DATE format is 'YYYY-MM-DD HH:MM:SS'.
SYSDATE and GETDATE are built-in functions and can be used anywhere a date expression may be used. They can be used in a INSERT...SELECT projection list, a WHERE clause or to insert values. They cannot be used with a SUM or AVG aggregate (operands must be numeric) or with a COUNT aggregate (column names are expected).
SYSDATE and GETDATE return the same DATE value in a single SQL statement context.
The literals TT_SYSDATE and ORA_SYSDATE are supported. TT_SYSDATE returns the TT_TIMESTAMP data type. ORA_SYSDATE returns the DATE data type.
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.
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.
TO_CHAR ( Expression1[, Expression2 [, Expression3]])
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. |
TO_CHAR supports different datetime format models depending on the data type specified for the expression. For information on the datetime format model used for TO_CHAR of data type DATE or TIMESTAMP, see "Datetime format models". For information on the datetime format model used for TO_CHAR of data type TT_DATE or TT_TIMESTAMP, see "Format model for ROUND and TRUNC date functions".
TO_CHAR supports different number format models depending on the numeric data type specified for the expression. For information on the number format model used for TO_CHAR of data type NUMBER or ORA_FLOAT, see "Number format models". For information on the number format model used for TO_CHAR of all other numeric data types, see "Format model for ROUND and TRUNC date functions".
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.
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.
TO_DATE (Expression1[, Expression2 [, Expression3]])
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. |
You can use a datetime format model with the TO_DATE function. For more information on datetime format models, see "Datetime format models".
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.
Converts an expression whose value is of type CHAR, VARCHAR2, NCHAR, NVARCHAR2, BINARY_FLOAT or BINARY_DOUBLE to a value of NUMBER type.
TO_NUMBER (Expression[, format])
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. |
You can use a number format model with the TO_NUMBER function. For more information on number format models, see "Number format models".
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.
The TRIM function trims leading or trailing characters (or both) from a character string.
There are four valid syntax options for TRIM:
You can specify one of the TRIM qualifiers (LEADING or TRAILING or BOTH) with the Trim_character:
TRIM ( LEADING|TRAILING|BOTH Trim_character FROM Expression )
You can specify one of the TRIM qualifiers (LEADING or TRAILING or BOTH) without the Trim_character:
TRIM ( LEADING|TRAILING|BOTH FROM Expression )
You can specify the Trim_character without one of the TRIM qualifiers:
TRIM (Trim_character FROM Expression )
You can specify the Expression without a qualifier or a Trim_character:
TRIM ( Expression )
TRIM has the parameters:
| Parameter | Description |
|---|---|
TRIM (
[
|
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 (
|
Removes both leading and trailing instances of Trim_character from Expression.
|
TRIM (
|
If you specify Expression without a qualifier or Trim_character, then leading and trailing blank spaces are removed from Expression.
|
If you specify the LEADING qualifier, TRIM removes any leading characters equal to Trim_character from Expression.
If you specify the TRAILING qualifier, TRIM removes any trailing characters equal to Trim_character from Expression.
If you specify the BOTH qualifier (or no qualifier), TRIM removes leading and trailing characters equal to Trim_character from Expression.
If you specify only Expression, then TRIM removes leading and trailing blank spaces.
If Expression is of type CHAR or VARCHAR2, the data type returned is VARCHAR2. If Expression is of type NCHAR or NVARCHAR2, the data type returned is NVARCHAR2. The returned data type length is equal to the data type length of Expression.
If Expression is a data type defined with CHAR length semantics, the returned length is expressed in CHAR length semantics.
If either Trim_character or Expression is NULL, the result is NULL.
You can specify TT_CHAR, TT_VARCHAR, TT_NCHAR, and TT_NVARCHAR for Trim_character and Expression. If Expression is of type TT_CHAR or TT_VARCHAR, the data type returned is TT_VARCHAR. If Expression is of type TT_NCHAR or TT_NVARCHAR, the data type returned is TT_NVARCHAR.
If Trim_character is of type NCHAR or NVARCHAR2 and Expression is of type CHAR or VARCHAR2, then Trim_character is demoted to CHAR or VARCHAR2 before TRIM is invoked. The conversion of Trim_character could be lost. If Trim_character is not in the database character set, then the query may produce unexpected results.
For CHAR, VARCHAR2, NCHAR, and NVARCHAR2 types:
If all the characters in Expression are removed by the TRIM function, then the result is NULL.
For TT_CHAR, TT_VARCHAR, TT_NCHAR and TT_NVARCHAR types:
If all the characters in Expression are removed by the TRIM function, then the result is the empty string.
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;
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.
TRUNC (date [,fmt])
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. |
For the permitted format models to use in fmt, see "Format model for ROUND and TRUNC date functions".
Command> SELECT TRUNC (TO_DATE ('27-OCT-92','DD-MON-YY'),'YEAR') FROM dual;
< 2092-01-01 00:00:00 >
1 row found.
Returns a number truncated to a certain number of decimal places.
TRUNC (Expression [,m])
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. |
SELECT TRUNC (15.79,1) FROM dual; < 15.7 > 1 row found. SELECT TRUNC (15.79,-1) FROM dual; < 10 > 1 row found.
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.
TT_HASH(Expression [,...])
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. |
Each expression must have a known data type and must be non-nullable. The hash value of the expression depends on both the value of the expression and its type. For example, TT_HASH of an TT_INTEGER with value 25 may be different from TT_HASH of a NUMBER or BINARY_DOUBLE with value 25. If you specify a list of expressions, the TT_HASH result depends on the order of the expressions in the list.
Since constants and expressions that are not simple column references are subject to internal typing rules, over which applications have no control, the best way to ensure that TT_HASH computes the desired value for expressions that are not simple column references is to CAST the expression to the desired type.
The result type of TT_HASH is TT_INTEGER in 32-bit mode and TT_BIGINT in 64 bit mode.
TT_HASH can be used in a SQL statement anywhere an expression can be used. For example, TT_HASH can be used in a SELECT list, a WHERE or HAVING clause, an ORDER BY clause, or a GROUP BY clause.
The output of error messages, trace messages, and ttAXactAdmin display the hash value as a signed decimal so that the value matches TT_HASH output.
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;
This function returns an integer (TT_INTEGER) that uniquely identifies the session user.
SELECT UID FROM dual; < 10 > 1 row found.
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.
UNISTR ('String')
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. |
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.
TimesTen supports these USER functions:
Each of these functions returns the name of the user that is currently connected to the TimesTen database.
Returns the name of the TimesTen user currently connected to the database.
CURRENT_USER
CURRENT_USER has no parameters.
To return the name of the user who is currently connected to the database:
SELECT CURRENT_USER FROM dual;
Returns the name of the TimesTen user who is currently connected to the database.
USER
USER has no parameters.
To return the name of the user who is currently connected to the database:
SELECT USER FROM dual;
Returns the name of the TimesTen user currently connected to the database.
SESSION_USER
SESSION_USER has no parameters.
To return the name of the session user:
SELECT SESSION_USER FROM dual;
Returns the name of the current database user as identified by the operating system.
SYSTEM_USER
SYSTEM_USER has no parameters.
To return the name of the operating system user:
SELECT SYSTEM_USER FROM dual;