3 Expressions

Expression specification

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

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

SQL syntax

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

or

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

or

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

or

Expression1 | | Expression2

or

Expression

Description

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

Examples

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

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

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

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

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

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

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

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

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

Examples of bitwise operators:

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

Subqueries

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

SQL syntax

[NOT] EXISTS | [NOT] IN (Subquery)
Expression {= | <> | > | >= | < | <= } [ANY | ALL] (Subquery)
Expression [NOT] IN (ValueList | Subquery)

Description

TimesTen supports queries with the characteristics listed in each section.

Table subqueries

• A subquery can appear in the WHERE clause or HAVING clause of any statement except one that creates a materialized view. Only one table subquery can be specified in a predicate. These predicates can be specified in a WHERE or HAVING clause, an OR expression within a WHERE or HAVING clause, or an ON clause of a joined table. They cannot be specified in a CASE expression, a materialized view, or a HAVING clause that uses the + operator for outer joins.

• A subquery can be specified in an EXISTS or NOT EXISTS predicate, a quantified predicate with ANY or ALL, or a comparison predicate. The allowed operators for both comparison and quantified predicates are: =, <, >, <=, >=, <>. The subquery cannot be connected to the outer query through a UNIQUE or NOT UNIQUE operator.

• Only one subquery can be specified in a quantified or comparison predicate. Specify the subquery as either the right operand or the left operand of the predicate, but not both.

• The subquery should not have an ORDER BY clause.

• FIRST NumRows is not supported in subquery statements.

• In a query specified in a quantified or comparison predicate, the underlying SELECT must have a single expression in the select list. In a query specified in a comparison predicate, if the underlying select returns a single row, the return value is the select result. If the underlying select returns no row, the return value is NULL. It is an error if the subquery returns multiple rows.

Scalar subqueries

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

Examples of supported subqueries for a list of customers having at least one unshipped order:

SELECT customers.name FROM customers
  WHERE EXISTS (SELECT 1 FROM orders
    WHERE customers.id = orders.custid
    AND orders.status = 'unshipped');

SELECT customers.name FROM customers
  WHERE customers.id = ANY 
    (SELECT orders.custid FROM orders
  WHERE orders.status = 'unshipped');

SELECT customers.name FROM customers 
  WHERE customers.id IN 
    (SELECT orders.custid FROM orders 
  WHERE orders.status = 'unshipped');

In this example, list items are shipped on the same date as when they are ordered:

SELECT line_items.id FROM line_items 
  WHERE line_items.ship_date = 
    (SELECT orders.order_date FROM orders 
  WHERE orders.id = line_items.order_id);

Aggregate functions

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

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

SQL syntax

{AVG   ({Expression | [ALL | DISTINCT] ColumnName})
 MAX   ({Expression | [ALL | DISTINCT] ColumnName | ROWID})
 MIN   ({Expression | [ALL | DISTINCT] ColumnName | ROWID})
 SUM   ({Expression | [ALL | DISTINCT] ColumnName})
 COUNT ({ * | [ALL | DISTINCT] ColumnName | ROWID})
}

Description

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

Examples

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

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

Calculate the MAX salary for employees in the HR schema:

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

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

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

Constants

A constant is a literal value.

SQL syntax

{IntegerValue | FloatValue |FloatingPointLiteral|
    FixedPointValue | 'CharacterString'|
    'NationalCharacterString' | 0xHexadecimalString |
    'DateString' | DateLiteral |'TimeString' | 
    TimeLiteral | 'TimestampString' | TimestampLiteral |
    IntervalLiteral | BINARY_FLOAT_INFINITY |
    BINARY_DOUBLE_INFINITY | -BINARY_FLOAT_INFINITY |
    -BINARY_DOUBLE_INFINITY | BINARY_FLOAT_NAN |
     BINARY_DOUBLE_NAN
}

N'Here''s how!'

N'This is an \u0061'

N'This is an a'

0xFFFAB0880088343330FFAA7

0x000A001231

'2007-01-27 12:00:00'

'2007-01-27'

DATE '2007-01-27' or DATE '2007-01-27 12:00:00'

TT_DATE '2007-01-27'. 

{d '2007-01-27'}. 

'20:25:30'

TIME '20:25:30'

INSERT INTO timetable VALUES (TIME '10:00:00');

SELECT * FROM timetable WHERE col1 < TIME '10:00:00';

{t '12:00:00'}

SELECT * FROM testable WHERE C1 = TIME '12:00:00'

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

{ts '9999-12-31 12:00:00'}

Format models

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

Number format models

Use number format models in the following functions:

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

Number format elements

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

The default american_america NLS language and territory setting is used.

Datetime format models

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.

Datetime format elements

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

Format model for ROUND and TRUNC date functions

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

Format model for TO_CHAR of TimesTen datetime data types

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

• If a numeric value does not fit in the specified format, TimesTen truncates the value.

• The format string cannot exceed 50 characters.

• D always results in a decimal point. Its value cannot be changed with an NLS parameter.

• If a float with an absolute value less than 1e-126 or greater than 1e126 is specified as input to the TO_CHAR function, TimesTen returns an error.

ABS

The ABS function returns the absolute value of Expression.

SQL syntax

ABS(Expression)

Parameters

ABS has the parameter:

Description

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

Examples

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

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

ADD_MONTHS

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

SQL syntax

ADD_MONTHS(date,integer)

Parameters

ADD_MONTHS has the parameters:

Description

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

Examples

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

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

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

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

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

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

ASCIISTR

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

SQL syntax

ASCIISTR ([N]'String')

Parameters

ASCIISTR has the parameter:

Description

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

Examples

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

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

CASE

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

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

SQL syntax

The syntax for a searched CASE expression is:

CASE
  {WHEN SearchCondition THEN Expression1}[…] 
   [ELSE Expression2]
END

The syntax for a simple CASE expression is:

CASE Expression 
  {WHEN CompExpression THEN Expression1}[…]
   [ELSE Expression2] 
END

Parameters

CASE has the parameters:

Description

CASE expression cannot be specified in the value clause of an INSERT statement.

Examples

To specify a searched CASE statement that specifies the value of a color, use:

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

To specify a simple CASE statement that specifies the value of a color, use:

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

CAST

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

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

The following conversions are supported:

• 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

SQL syntax

CAST
  ( {Expression | NULL} AS DataType )

Parameters

CAST has the parameters:

Description

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

Examples

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

CHR

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

SQL syntax

CHR(n)

Parameters

CHR has the parameter:

Description

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

Examples

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

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

CEIL

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

SQL syntax

CEIL(Expression)

Parameters

CEIL has the parameter:

Description

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

Examples

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

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

COALESCE

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

SQL syntax

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

Parameters

COALESCE has the parameters:

Description

• 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
  

Examples

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

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

CONCAT

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

SQL syntax

CONCAT(Expression1, Expression2)

Parameters

CONCAT has the parameters:

Description

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

Examples

The following example concatenates first names and last names.

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

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

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

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

DECODE

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

SQL syntax

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

Parameters

DECODE has the parameters:

Description

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

Examples

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

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

EXTRACT

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

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

SQL syntax

EXTRACT (DateTimeField FROM IntervalExpression | DateTimeExpression)

Parameters

EXTRACT has the following parameters:

Description

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

Examples

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

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

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

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

FLOOR

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

SQL syntax

FLOOR (Expression)

Parameters

FLOOR has the parameter:

Description

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

Examples

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

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

GREATEST

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

SQL syntax

GREATEST (Expression [,...])

Parameters

GREATEST has the parameter:

Description

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

LEAST

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

SQL syntax

LEAST (Expression [,...])

Parameters

LEAST has the parameter:

Description

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

LOWER and UPPER

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

SQL syntax

{UPPER | LOWER} (Expression1)

Parameters

LOWER and UPPER have the following parameter:

Description

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

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

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

LPAD

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

SQL syntax

LPAD (Expression1, n [,Expression2])

Parameters

LPAD has the parameters:

Description

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

Examples

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

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

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

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

LTRIM

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

SQL syntax

LTRIM (Expression1 [,Expression2])

Parameters

LTRIM has the parameters:

Description

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

Examples

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

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

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

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

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

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

MOD

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

SQL syntax

MOD(Expression1, Expression2)

Parameters

MOD has the following parameters:

Description

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

NCHR

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

SQL syntax

NCHR(n)

Parameters

NCHR has the parameter:

The following example returns the NCHAR character 187:

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

Example

The following example returns the NCHAR character 187:

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

NLSSORT

Returns the sort key value for the given string.

SQL syntax

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

Parameters

NLSSORT has the following parameters:

Description

• The returned sort key value is of type VARBINARY.

• You can create a linguistic index for linguistic comparisons.

Examples

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

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

The following example creates a linguistic index:

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

NUMTODSINTERVAL

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

SQL syntax

NUMTODSINTERVAL (Expression1, IntervalUnit)

Parameters

NUMTODSINTERVAL has the parameters:

Examples

Example using NUMTODSINTERVAL with SYSDATE:

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

NUMTOYMINTERVAL

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

SQL syntax

NUMTOYMINTERVAL (Expression1, 'IntervalUnit')

Parameters

NUMTOYMINTERVAL has the parameters:

Examples

An example using NUMTOYMINTERVAL:

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

NVL

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

SQL syntax

NVL(Expression1, Expression2)

Parameters

NVL has the parameters:

Description

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

Examples

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

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

POWER

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

SQL syntax

POWER (Base, Exponent)

Parameters

POWER has the parameters:

Description

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

Example

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

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

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

ROUND (date)

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

SQL syntax

ROUND (date [,fmt]) 

Parameters

ROUND (date) has the parameters:

Description

• 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".

Examples

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

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

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

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

ROUND (expression)

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

SQL syntax

ROUND (Expression1 [,Expression2])

Parameters

ROUND has the parameters:

Description

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

Examples

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

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

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

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

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

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

RPAD

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

SQL syntax

RPAD (Expression1, n [,Expression2])

Parameters

RPAD has the parameters:

Description

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

Examples

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

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

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

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

RTRIM

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

SQL syntax

RTRIM (Expression1 [,Expression2])

Parameters

RTRIM has the parameters:

Description

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

Examples

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

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

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

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

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

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

SIGN

The SIGN function returns the sign of Expression.

SQL syntax

SIGN (Expression)

Parameters

SIGN has the parameter:

Description

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

Examples

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

Command> DESCRIBE signex;

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

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

SQRT

The SQRT function returns the square root of Expression.

SQL syntax

SQRT(Expression)

Parameters

SQRT has the parameter:

Description

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

Examples

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

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

String functions

TimesTen supports these string functions in SELECT statements:

• SUBSTR, SUBSTRB, SUBSTR4

• INSTR, INSTRB, INSTR4

• LENGTH, LENGTHB, LENGTH4

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

SUBSTR, SUBSTRB, SUBSTR4

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

SQL syntax

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

Parameters

SUBSTR has the parameters:

Description

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

Examples

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

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

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

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

INSTR, INSTRB, INSTR4

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

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

SQL syntax

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

Parameters

INSTR has the parameters:

Description

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

Examples

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

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

LENGTH, LENGTHB, LENGTH4

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

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

SQL syntax

{LENGTH|LENGTHB|LENGTH4} (CharExpr) 

Parameters

LENGTH has the parameter:

Description

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

Examples

Determine the length of the string 'William':

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

SYS_CONTEXT

Returns information about the current session.

The data type of the return value is VARCHAR2.

SQL syntax

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

Parameters

SYS_CONTEXT has the parameters:

These are descriptions of the supported values for parameter:

Description

The data type of the return value is VARCHAR2.

Examples

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

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

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

SYSDATE and GETDATE

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

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

SQL syntax

SYSDATE | GETDATE( )

Parameters

The SYSDATE and GETDATE functions have no parameters.

Description

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

Examples

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

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

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

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

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

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

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

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

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

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

TO_CHAR

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

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

SQL syntax

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

Parameters

TO_CHAR has the parameters:

Description

• 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".

Examples

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

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

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

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

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

TO_DATE

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

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

SQL syntax

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

Parameters

TO_DATE has the parameters:

Description

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

Examples

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

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

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

TO_NUMBER

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

SQL syntax

TO_NUMBER (Expression[, format]) 

Parameters

TO_NUMBER has the parameters:

Description

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

Examples

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

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

TRIM

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

SQL syntax

There are four valid syntax options for TRIM:

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

Parameters

TRIM has the parameters:

Description

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

Examples

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

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

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

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

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

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

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

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

TRUNC (date)

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

SQL syntax

TRUNC (date [,fmt]) 

Parameters

TRUNC (date) has the parameters:

Description

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

Examples

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

TRUNC (expression)

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

SQL syntax

TRUNC (Expression [,m]) 

Parameters

TRUNC has the parameters:

Examples

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

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

TT_HASH

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

SQL syntax

TT_HASH(Expression [,...])

Parameters

TT_HASH has the parameter:

Description

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

Examples

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

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

UID

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

Examples

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

UNISTR

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

SQL syntax

UNISTR ('String')

Parameters

UNISTR has the parameter:

Examples

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

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

USER functions

TimesTen supports these USER functions:

• CURRENT_USER

• USER

• SESSION_USER

• SYSTEM_USER

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

CURRENT_USER

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

SQL syntax

CURRENT_USER

Parameters

CURRENT_USER has no parameters.

Examples

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

SELECT CURRENT_USER FROM dual;

USER

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

SQL syntax

USER

Parameters

USER has no parameters.

Examples

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

SELECT USER FROM dual;

SESSION_USER

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

SQL syntax

SESSION_USER

Parameters

SESSION_USER has no parameters.

Examples

To return the name of the session user:

SELECT SESSION_USER FROM dual;

SYSTEM_USER

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

SQL syntax

SYSTEM_USER

Parameters

SYSTEM_USER has no parameters.

Examples

To return the name of the operating system user:

SELECT SYSTEM_USER FROM dual;