4 Functions

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, TIMESTAMP, ORA_TIMESTAMP and ORA_DATE.

• Data types TIME, TT_TIME, 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 any 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 enables 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.

AVG

Computes the arithmetic mean of the values in the argument. Null values are ignored.

SQL syntax

AVG ([ALL | DISTINCT] Expression) [OVER ([AnalyticClause])]

Parameters

AVG has the following parameters:

Description

• If AVG is computed over an empty table in which GROUP BY is not used, then AVG returns NULL.

• If AVG is computed over an empty group or an empty grouped table (GROUP BY is used), AVG returns nothing.

• AVG is evaluated as SUM/COUNT. The result data type is derived using the rule that is applied for the DIV operator.

• If you do not specify the AnalyticClause in your query, then AVG acts as an aggregate function.

• If you specify DISTINCT and the AnalyticClause, then you can only specify the QueryPartitionClause. The OrderByClause and WindowingClause are not allowed.

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 >

CAST

Enables 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 data type. CAST is not needed to convert a NULL literal to the desired target type.

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 system 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 expressions. 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 treatment of NCHAR and NVARCHAR2 is similar. If one of the operands is of varying length, the result is of varying length. Otherwise the result is of a fixed length.

• The return data type of CONCAT depends on the types of Expression1 and Expression2. In concatenations of two different data types, the database returns the data type that can contain the result. Therefore, if one of the arguments is a national character data type, the returned value is a national character data type. If one of the arguments is a LOB, the returned value is a LOB.

  The following table provides examples of 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)
  CLOB	NCLOB	NCLOB
  NCLOB	NCHAR	NCLOB
  NCLOB	CHAR(n)	NCLOB
  NCHAR(n)	CLOB	NCLOB

  
  

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

COUNT

Counts all rows that satisfy the WHERE clause, including rows containing null values. The data type of the result is TT_INTEGER.

COUNT is an aggregate function and can also be an aggregate analytic function. For more details on aggregate functions, see "Aggregate functions". For information on analytic functions, see "Analytic functions". 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 Views Reference.

SQL syntax

COUNT ({* | [ALL | DISTINCT]{Expression|ROWID}}) 
[OVER ([AnalyticClause])]

Parameters

COUNT has the parameters:

Description

• If an aggregate function is computed over an empty table in which GROUP BY is not used, COUNT returns 0.

• If an aggregate function is computed over an empty group or an empty grouped table (GROUP BY is used), COUNT returns nothing.

• If you do not use the AnalyticClause in your query, then COUNT acts as an aggregate function.

• If you specify DISTINCT and the AnalyticClause, then you can only specify the QueryPartitionClause. The OrderByClause and WindowingClause are not allowed.

Examples

Count the number of employees.

Command> SELECT COUNT(*) "TOTAL EMP" FROM employees;
 
TOTAL EMP
< 107 >
1 row found.

Count the number of managers by selecting out each individual manager ID without duplication.

Command> SELECT COUNT(DISTINCT manager_id) "Managers" FROM employees;
 
MANAGERS
< 18 >
1 row found.

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;

DECODE

The DECODE function compares an expression to each search value one by one. If the expression is equal to the search value, 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', the function returns 'Italy'. If the country_id is equal to 'JP', the function returns 'Japan'. If the country_id is equal to 'US', 'United States' is returned. If the country_id is not equal to 'IT' or 'JP' or 'US', the function returns 'Other'.

Command> SELECT location_id, 
> DECODE (country_id, 'IT', 'Italy', 
> 'JP', 'Japan', 'US', 'United States', '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.

DENSE_RANK

The DENSE_RANK function is an analytic function that computes the rank of rows in an ordered group of rows and returns the ranks as type NUMBER.

SQL syntax

DENSE_RANK () OVER ( [QueryPartitionClause] OrderByClause )

Parameters

DENSE_RANK has the parameters:

Description

• The ranks are consecutive integers beginning with 1. The largest rank value is the number of unique values returned by the query. Rank values are not skipped in the event of ties. Rows with equal values for the ranking criteria receive the same rank.

• DENSE_RANK computes the rank of each row returned from a query with respect to the other rows, based on the values of the Expressions in the OrderByClause.

Example

Select the department name, employee name, and salary of all employees who work in the human resources or purchasing department. Compute a rank for each unique salary in each of the two departments. The salaries that are equal receive the same rank.

Command> SELECT d.department_name, e.last_name, e.salary, DENSE_RANK()
       >  OVER (PARTITION BY e.department_id ORDER BY e.salary) AS dense
       > FROM employees e, departments d
       > WHERE e.department_id = d.department_id
       >    AND d.department_id IN ('30', '40')
       > ORDER BY e.last_name, e.salary, d.department_name, dense;
< Purchasing, Baida, 2900, 4 >
< Purchasing, Colmenares, 2500, 1 >
< Purchasing, Himuro, 2600, 2 >
< Purchasing, Khoo, 3100, 5 >
< Human Resources, Mavris, 6500, 1 >
< Purchasing, Raphaely, 11000, 6 >
< Purchasing, Tobias, 2800, 3 >
7 rows found.

EMPTY_BLOB

A BLOB column can be initialized to a zero-length, empty BLOB using the EMPTY_BLOB function. This function initializes the LOB to a non-null value, so can be used for initializing any BLOB that has been declared as NOT NULL.

SQL syntax

EMPTY_BLOB ()

Parameters

EMPTY_BLOB has no parameters.

Description

You can only use EMPTY_BLOB in the VALUES clause of an INSERT statement or the SET clause of an UPDATE statement.

Examples

The following example uses the EMPTY_BLOB function to initialize a non-null BLOB column to a zero-length value.

Command> CREATE TABLE blob_content (
 > id NUMBER PRIMARY KEY, 
 > blob_column BLOB NOT NULL); -- Does not allow a NULL value

Command> INSERT INTO blob_content (id, blob_column) 
 > VALUES (1, EMPTY_BLOB( ) );
1 row inserted.

EMPTY_CLOB

A CLOB or NCLOB column can be initialized to a zero-length, empty CLOB or NCLOB using the EMPTY_CLOB function. Both CLOB and NCLOB data types are initialized with the EMPTY_CLOB function. This function initializes the LOB to a non-null value, so can be used for initializing any CLOB or NCLOB that has been declared as NOT NULL.

SQL syntax

EMPTY_CLOB () 

Parameters

EMPTY_CLOB has no parameters.

Description

You can only use EMPTY_CLOB in the VALUES clause of an INSERT statement or the SET clause of an UPDATE statement.

Examples

The following example uses the EMPTY_CLOB function to initialize a non-null CLOB column to a zero-length value.

Command> CREATE TABLE clob_content ( 
 > id NUMBER PRIMARY KEY, 
 > clob_column CLOB NOT NULL ); -- This definition does not allow a NULL value

Command> INSERT INTO clob_content (id, clob_column)
       > VALUES (1, EMPTY_CLOB( ) );
1 row inserted.

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.

FIRST_VALUE

The FIRST_VALUE function is an analytic function that returns the first value in an ordered set of values.

SQL syntax

FIRST_VALUE (Expression [IGNORE NULLS]) OVER (AnalyticClause)

Parameters

FIRST_VALUE has the parameters:

Description

• If the first value in the set is NULL, then FIRST_VALUE returns NULL unless you specify IGNORE NULLS. Specify IGNORE NULLS if you want the function to return the first non-null value in the set or NULL if all values in the set are NULL.

Example

Use the FIRST_VALUE function to select for each employee in department 90, the last name of the employee with the lowest salary.

Command> SELECT department_id, last_name, salary, FIRST_VALUE (last_name) OVER
       >   (ORDER BY salary ASC ROWS UNBOUNDED PRECEDING) AS lowest_sal
       > FROM
       >  (SELECT * FROM employees WHERE department_id = 90 ORDER BY employee_id)
       > ORDER BY department_id, last_name, salary, lowest_sal;
< 90, De Haan, 17000, Kochhar >
< 90, King, 24000, Kochhar >
< 90, Kochhar, 17000, Kochhar >
3 rows 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.

GROUP_ID

The GROUP_ID function identifies duplicate groups in a SELECT query resulting from a GROUP BY clause. This function returns the number 0 for a unique group; any subsequent duplicate grouping row receives a higher number, starting with 1. The GROUP_ID function filters out duplicate groupings from the query result. If you have complicated queries that may generate duplicate values, you can eliminate those rows by including the HAVING GROUP_ID() = 0 condition.

Syntax

The GROUP_ID function is applicable only in a SELECT statement that contains a GROUP BY clause; it can be used in the select list and HAVING clause of the SELECT query.

GROUP_ID()

Parameters

GROUP_ID has no parameters.

Example

The following example shows how GROUP_ID returns 0 for a unique group and a number > 0 to identify duplicate groups. The following example prints out the department number, manager id and the sum of the salary within the manager. The resulting output is grouped using the ROLLUP clause on the manager and department providing superaggregate results.

Command> SELECT department_id as DEPT, manager_id AS MGR, 
 GROUP_ID(), SUM(salary) as SALARY
 FROM employees 
 WHERE manager_id > 146 
 GROUP BY manager_id, ROLLUP(manager_id, department_id) 
 ORDER BY manager_id, department_id;
 
DEPT, MGR, EXP, SALARY
< 80, 147, 0, 46600 >
< <NULL>, 147, 1, 46600 >
< <NULL>, 147, 0, 46600 >
< 80, 148, 0, 51900 >
< <NULL>, 148, 0, 51900 >
< <NULL>, 148, 1, 51900 >
< 80, 149, 0, 43000 >
< <NULL>, 149, 0, 7000 >
< <NULL>, 149, 0, 50000 >
< <NULL>, 149, 1, 50000 >
< 20, 201, 0, 6000 >
< <NULL>, 201, 0, 6000 >
< <NULL>, 201, 1, 6000 >
< 110, 205, 0, 8300 >
< <NULL>, 205, 0, 8300 >
< <NULL>, 205, 1, 8300 >
16 rows found.

GROUPING

The GROUPING function enables you to determine whether a NULL is a stored NULL or an indication of a subtotal or grand total. Using a single column as its argument, GROUPING returns a 1 when it encounters a null value created by a ROLLUP or CUBE operation, indicating a subtotal or grand total. Any other type of value, including a stored NULL, returns a 0.

Syntax

The GROUPING function is applicable only in a SELECT statement that contains a GROUP BY clause. It can be used in the select list and HAVING clause of the SELECT query that includes the GROUP BY clause. The expression indicated in the GROUPING function syntax must match one of the expressions contained in the GROUP BY clause.

The following syntax uses GROUPING to identify the results from the expression listed as an aggregate or not:

SELECT …  [GROUPING(Expression)…]  … 
  GROUP BY … { RollupCubeClause | GroupingSetsClause } ...

The following syntax uses GROUPING within a HAVING clause to identify the results from the expression listed as an aggregate or not:

SELECT …
  GROUP BY … { RollupCubeClause | GroupingSetsClause } ...
 HAVING GROUPING(Expression) = 1

Parameters

Examples

The following example shows how the grouping function returns a '1' when it encounters the grand total for the department.

Command> SELECT department_id AS DEPT, 
GROUPING(department_id) AS DEPT_GRP, SUM(salary) AS SALARY 
FROM emp_details_view 
GROUP BY ROLLUP(department_id) 
ORDER BY department_id; 
DEPT, DEPT_GRP, SALARY
< 10, 0, 4400 >
< 20, 0, 19000 >
< 30, 0, 24900 >
< 40, 0, 6500 >
< 50, 0, 156400 >
< 60, 0, 28800 >
< 70, 0, 10000 >
< 80, 0, 304500 >
< 90, 0, 58000 >
< 100, 0, 51600 >
< 110, 0, 20300 >
< <NULL>, 1, 684400 >
12 rows found.

The following example shows that you can use the GROUPING function for each column to determine which null values are for the totals.

Command> SELECT department_id AS DEPT, job_id AS JOB, 
GROUPING(department_id) AS DEPT_GRP, GROUPING(job_id) AS JOB_GRP, 
GROUPING_ID(department_id, job_id) AS GRP_ID, SUM(salary) AS SALARY 
FROM emp_details_view 
GROUP BY CUBE(department_id, job_id) 
ORDER BY department_id, job_id, grp_id ASC;
 
DEPT, JOB, DEPT_GRP, JOB_GRP, GRP_ID, SALARY
< 10, AD_ASST, 0, 0, 0, 4400 >
< 10, <NULL>, 0, 1, 1, 4400 >
< 20, MK_MAN, 0, 0, 0, 13000 >
< 20, MK_REP, 0, 0, 0, 6000 >
< 20, <NULL>, 0, 1, 1, 19000 >
< 30, PU_CLERK, 0, 0, 0, 13900 >
< 30, PU_MAN, 0, 0, 0, 11000 >
< 30, <NULL>, 0, 1, 1, 24900 >
...
< 110, AC_ACCOUNT, 0, 0, 0, 8300 >
< 110, AC_MGR, 0, 0, 0, 12000 >
< 110, <NULL>, 0, 1, 1, 20300 >
< <NULL>, AC_ACCOUNT, 1, 0, 2, 8300 >
< <NULL>, AC_MGR, 1, 0, 2, 12000 >
< <NULL>, AD_ASST, 1, 0, 2, 4400 >
< <NULL>, AD_PRES, 1, 0, 2, 24000 >
< <NULL>, AD_VP, 1, 0, 2, 34000 >
< <NULL>, FI_ACCOUNT, 1, 0, 2, 39600 >
< <NULL>, FI_MGR, 1, 0, 2, 12000 >
< <NULL>, HR_REP, 1, 0, 2, 6500 >
< <NULL>, IT_PROG, 1, 0, 2, 28800 >
< <NULL>, MK_MAN, 1, 0, 2, 13000 >
< <NULL>, MK_REP, 1, 0, 2, 6000 >
< <NULL>, PR_REP, 1, 0, 2, 10000 >
< <NULL>, PU_CLERK, 1, 0, 2, 13900 >
< <NULL>, PU_MAN, 1, 0, 2, 11000 >
< <NULL>, SA_MAN, 1, 0, 2, 61000 >
< <NULL>, SA_REP, 1, 0, 2, 243500 >
< <NULL>, SH_CLERK, 1, 0, 2, 64300 >
< <NULL>, ST_CLERK, 1, 0, 2, 55700 >
< <NULL>, ST_MAN, 1, 0, 2, 36400 >
< <NULL>, <NULL>, 1, 1, 3, 684400 >
50 rows found.

GROUPING_ID

The GROUPING_ID function returns a number that shows the exact GROUP BY level of aggregation resulting from a ROLLUP or CUBE clause.

The GROUPING_ID function takes the ordered list of grouping columns from the ROLLUP or CUBE as input and computes the grouping ID as follows:

1. Applies the GROUPING function to each of the individual columns in the list. The result is a set of ones and zeros, where 1 represents a superaggregate generated by the ROLLUP or CUBE.

2. Puts these ones and zeros in the same order as the order of the columns in its argument list to produce a bit vector.

3. Converts this bit vector from a binary number into a decimal (base 10) number, which is returned as the grouping ID.

For instance, if you group with CUBE(department_id, job_id), the returned values are as shown in Table 4-1.

The GROUPING_ID function can be used in a query to filter rows so that only the summary rows are displayed. You can use the GROUPING_ID function in the HAVING clause to restrict output to only those rows that contain totals and subtotals. This can be accomplished when adding a comparison of the GROUPING_ID function results as greater than zero in the HAVING clause.

Syntax

The GROUPING_ID function is applicable only in a SELECT statement that contains the GROUP BY clause, a GROUPING function, and one of the following clauses: ROLLUP, CUBE or GROUPING SETS clauses. It can be used in the select list and HAVING clause of the SELECT query.

GROUPING_ID(Expression [, Expression ]...)

Parameters

Examples

The following example has the HAVING clause filter on the GROUPING_ID function, where the returned value is greater than zero. This excludes rows that do not contain either a subtotal or grand total. The following example shows the subtotals for the departments are identified with a group ID of 1, subtotals for the job ID with a group ID of 2 and the grand total with a group ID of 3:

Command> SELECT department_id AS DEPT, job_id AS JOB, 
GROUPING_ID(department_id, job_id) AS GRP_ID, 
SUM(salary) AS SALARY 
FROM emp_details_view 
GROUP BY CUBE(department_id, job_id) 
HAVING GROUPING_ID(department_id, job_id) > 0 
ORDER BY department_id, job_id, grp_id ASC;
 
DEPT, JOB, GRP_ID, SALARY
< 10, <NULL>, 1, 4400 >
< 20, <NULL>, 1, 19000 >
< 30, <NULL>, 1, 24900 >
< 40, <NULL>, 1, 6500 >
< 50, <NULL>, 1, 156400 >
< 60, <NULL>, 1, 28800 >
< 70, <NULL>, 1, 10000 >
< 80, <NULL>, 1, 304500 >
< 90, <NULL>, 1, 58000 >
< 100, <NULL>, 1, 51600 >
< 110, <NULL>, 1, 20300 >
< <NULL>, AC_ACCOUNT, 2, 8300 >
< <NULL>, AC_MGR, 2, 12000 >
< <NULL>, AD_ASST, 2, 4400 >
< <NULL>, AD_PRES, 2, 24000 >
< <NULL>, AD_VP, 2, 34000 >
< <NULL>, FI_ACCOUNT, 2, 39600 >
< <NULL>, FI_MGR, 2, 12000 >
< <NULL>, HR_REP, 2, 6500 >
< <NULL>, IT_PROG, 2, 28800 >
< <NULL>, MK_MAN, 2, 13000 >
< <NULL>, MK_REP, 2, 6000 >
< <NULL>, PR_REP, 2, 10000 >
< <NULL>, PU_CLERK, 2, 13900 >
< <NULL>, PU_MAN, 2, 11000 >
< <NULL>, SA_MAN, 2, 61000 >
< <NULL>, SA_REP, 2, 243500 >
< <NULL>, SH_CLERK, 2, 64300 >
< <NULL>, ST_CLERK, 2, 55700 >
< <NULL>, ST_MAN, 2, 36400 >
< <NULL>, <NULL>, 3, 684400 >
31 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, 0 is returned. The position returned is always relative to the beginning of SourceExpr. 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} ( SourceExpr, SearchExpr [,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.

The following example uses INSTR to provide the number of employees with a '650' area code as input to the COUNT function:

Command> SELECT COUNT(INSTR(phone_number, '650')) FROM employees;
< 107 >
1 row found.

LAST_VALUE

The LAST_VALUE function is an analytic function that returns the last value in an ordered set of values.

SQL syntax

LAST_VALUE (Expression [IGNORE NULLS]) OVER (AnalyticClause)

Parameters

LAST_VALUE has the parameters:

Description

• If the last value in the set is NULL, then LAST_VALUE returns NULL unless you specify IGNORE NULLS. Specify IGNORE NULLS if you want the function to return the last non-null value in the set or NULL if all values in the set are NULL.

Example

Use the LAST_VALUE function to return for each row the hire date of the employee with the highest salary.

Command> SELECT last_name, salary, hire_date,
       >  LAST_VALUE (hire_date) OVER (ORDER BY salary
       >    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lv
       > FROM
       > (SELECT * FROM employees WHERE department_id = 90 ORDER BY hire_date)
       > ORDER BY last_name, salary, hire_date, lv;
< De Haan, 17000, 1993-01-13 00:00:00, 1987-06-17 00:00:00 >
< King, 24000, 1987-06-17 00:00:00, 1987-06-17 00:00:00 >
< Kochhar, 17000, 1989-09-21 00:00:00, 1987-06-17 00:00:00 >
3 rows 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.

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.

The following determines the length of the NCLOB data:

Command> SELECT nclob_column FROM nclob_content;
< Demonstration of the NCLOB data type >
1 row found.

Command> SELECT LENGTH(nclob_column) FROM nclob_content;
< 36 >
1 row found.

LOWER and UPPER

The LOWER function converts expressions of type CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB, or NCLOB to lowercase. The UPPER function converts expressions of type CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB, or NCLOB to uppercase. Character semantics are 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. If Expression1 is a LOB, the data type returned is the same as the LOB data type provided.

• 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, NVARCHAR2, CLOB or NCLOB data types:

  • If either Expression1 or Expression2 is NULL, the result is NULL. If n is less than or equal to 0, 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, the result is the empty string.

Examples

The following prints out the last names of the first 5 employees, left-padded with periods out to 20 characters.

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

Use LPAD function to left-pad the string 'LPAD Function' with string 'DEMO-ONLY' plus 2 spaces. The DEMO-ONLY string is replicated as much as it can as defined by the total characters output by the function, which is replicated three 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. 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. If Expression1 is a CLOB or NCLOB, the data type returned is the same as the LOB data type provided. 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, NVARCHAR2, CLOB or NCLOB types:

  • If all the characters in Expression1 are removed by the LTRIM function, 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, 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.

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. MAX is an aggregate function and can also be an aggregate analytic function. For more details on aggregate functions, see "Aggregate functions". For more information on analytic functions, see "Analytic functions".

SQL syntax

MAX ([ALL | DISTINCT]{Expression | ROWID}) [OVER ([AnalyticClause])]

Parameters

MAX has the parameters:

Description

• If MAX is computed over an empty table in which GROUP BY is not used, MAX returns NULL.

• If MAX is computed over an empty group or an empty grouped table (GROUP BY is used), MAX returns nothing.

• The result data type is the same as the source.

• If you do not use the AnalyticClause in your query, then MAX acts as an aggregate function.

Examples

Find the largest salary:

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

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. For more details on aggregate functions, see "Aggregate functions". MIN can also be an aggregate analytic function. For information on analytic functions, see "Analytic functions".

SQL syntax

 MIN ([ALL | DISTINCT]{Expression|ROWID}) [OVER (AnalyticClause)]

Parameters

MIN has the parameters:

Description

• If the MIN function is computed over an empty table in which GROUP BY is not used, MIN returns NULL.

• If the MIN function is computed over an empty group or an empty grouped table (GROUP BY is used), MIN returns nothing.

• The result data type is the same as the source.

• If you do not use the AnalyticClause in your query, then MIN acts as an aggregate function.

Examples

Show the smallest salary:

Command> SELECT MIN(salary) "Min Salary" FROM employees;
 
MIN SALARY
< 2100 >

Show the earliest hire date:

1 row found.

Command> SELECT MIN(hire_date) "Earliest Hire Date" FROM employees;
 
EARLIEST HIRE DATE
< 1987-06-17 00:00:00 >
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;

MONTHS_BETWEEN

The MONTHS_BETWEEN function returns number of months between dates date1 and date2.

SQL syntax

MONTHS_BETWEEN(date1, date2)

Parameters

MONTHS_BETWEEN has the parameters:

Description

Input parameters can be any combination of all supported datetime data types, excluding the TIME or TT_TIME data types. The supported datetime data types include DATE, TIMESTAMP, TT_DATE, TT_TIMESTAMP, ORA_DATE, and ORA_TIMESTAMP. For details on all datetime data types, see Chapter 1, "Data Types".

The return data type is a NUMBER.

MONTHS_BETWEEN returns number of months between dates date1 and date2.

• If date1 is later than date2, the returned result is positive.

• If date1 is earlier than date2, the returned result is negative.

• If date1 and date2 are both either the same day of the month or the last day of the month, the returned result is an integer. For all other cases, the returned result is a fraction based on a 31-day month that considers the difference in time components for date1 and date2 parameters.

Examples

The following examples calculate months between two given dates.

Command> SELECT MONTHS_BETWEEN(DATE '1995-02-02', DATE '1995-01-01') 
AS Months FROM dual;
 
MONTHS
< 1.03225806451613 >
1 row found. 

Command> SELECT MONTHS_BETWEEN(DATE '2010-02-02', DATE '2010-10-01') "Months"
 FROM dual;
 
MONTHS
< -7.96774193548387 >
1 row found.
 

The following command uses CAST to explicitly convert CHAR strings into timestamps. The first result is rounded to an integer.

Command> SELECT ROUND ( MONTHS_BETWEEN (CAST ('2010-04-15 14:13:52' 
AS TIMESTAMP), CAST ('2000-12-31 00:00:00' AS TIMESTAMP))), 
MONTHS_BETWEEN (CAST ('2010-04-15 14:13:52' AS TIMESTAMP), 
CAST ('2000-12-31 00:00:00' AS TIMESTAMP)) 
FROM dual;

< 112, 111.502998805257 >
1 row found.

NCHR

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

SQL syntax

NCHR(n)

Parameters

NCHR has the parameter:

Example

The following example returns the NCHAR character 187:

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

NLS_CHARSET_ID

NLS_CHARSET_ID returns the character set ID number corresponding to the character set name.

SQL syntax

NLS_CHARSET_ID(String)

Parameters

NLS_CHARSET_ID has the parameter:

Examples

The following example returns the character set ID number of character set JA16EUC:

SELECT NLS_CHARSET_ID('JA16EUC') FROM dual; 
< 830 >
1 row found.

NLS_CHARSET_NAME

NLS_CHARSET_NAME returns the name of the character set corresponding to the character set ID number.

SQL syntax

NLS_CHARSET_NAME(Number)

Parameters

NLS_CHARSET_NAME has the parameter:

Description

The character set name is returned as a VARCHAR2 value in the database character set.

Examples

The following example returns the character set name corresponding to character set ID number 2:

SELECT NLS_CHARSET_NAME(2) FROM dual;
< WE8DEC >
1 row found.

The following example determines the name of the database character set by providing CHAR_CS as the character set name within the NLS_CHARSET_ID function, whose results are provided to the NLS_CHARSET_NAME function:

SELECT NLS_CHARSET_NAME(NLS_CHARSET_ID('CHAR_CS')) FROM dual;
< US7ASCII >
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 Name > 'Gaberd';
< Gardiner >
1 row 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';
Session altered.
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 range index on columns:
    NLSSORT(NAME,'NLS_SORT = XDanish')
  1 index found.
1 index found on 1 table.

NULLIF

NULLIF compares two expressions. If the values are equal, NULLIF returns a NULL; otherwise, the function returns the first expression.

SQL syntax

NULLIF(Expression1, Expression2)

Parameters

NULLIF has the following parameters:

Description

• If both parameters are numeric data types, Timesten determines the argument with the higher numeric precedence, implicitly converts the other argument to this data type, and returns this data type. If the parameters are not numeric data types, they must be in the same data type family.

• LOB data types are not supported in NULLIF. The TIME data type is only supported if both columns are of the TIME data type.

• The NULLIF function is logically equivalent to the following CASE expression:

  CASE WHEN Expression1 = Expression2 THEN NULL ELSE Expression1 END
  

Examples

The following example selects employees who have changed jobs since they were hired, which is indicated by a different job_id in the job_history table from the current job_id in the employees table. Thus, when you apply NULLIF to the old and new job_id entries, those that are the same returns a NULL; those that are different indicate those employees who have changed jobs.

Command> SELECT e.last_name, NULLIF(e.job_id, j.job_id) "Old Job ID" 
 FROM employees e, job_history j 
 WHERE e.employee_id = j.employee_id 
 ORDER BY last_name, "Old Job ID";

< De Haan, AD_VP >
< Hartstein, MK_MAN >
< Kaufling, ST_MAN >
< Kochhar, AD_VP >
< Kochhar, AD_VP >
< Raphaely, PU_MAN >
< Taylor, SA_REP >
< Taylor, <NULL> >
< Whalen, AD_ASST >
< Whalen, <NULL> >
10 rows 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 the data types are different, the data types are implicitly converted, if possible. If they cannot be implicitly converted, an error is returned.

  The following describes how the implicit conversion of data types is performed:

  • If Expression1 is character data, then Expression2 is converted to the same data type of Expression1 and returns the result in a VARCHAR2 in the character set of Expression1.

  • If Expression1 is numeric data, then TimesTen determines which expression has the highest numeric precedence and implicitly converts the other argument to that data type, which is also the data type that is returned.

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

RANK

The RANK function is an analytic function that calculates the rank of a value in a group of values.

SQL syntax

RANK () OVER ( [QueryPartitionClause] OrderByClause )

Parameters

RANK has the parameters:

Description

• The return type is NUMBER.

• Rows with equal values for the ranking criteria receive the same rank. TimesTen then adds the number of tied rows to the ties rank to calculate the next rank. Therefore, the ranks may not be consecutive numbers.

• RANK computes the rank of each row returned from a query with respect to the other rows returned by the query, based on the values of the expressions in the OrderByClause.

Example

Use the RANK function to rank the first 10 employees in department 80 based on their salary and commission. Identical salary values receive the same rank and cause nonconsecutive ranks.

Command> SELECT first 10 department_id, last_name, salary, commission_pct,
       >  RANK() OVER (PARTITION BY department_id
       >    ORDER BY salary DESC, commission_pct) "Rank"
       > FROM employees WHERE department_id = 80
       > ORDER BY department_id, last_name, salary, commission_pct, "Rank";
< 80, Abel, 11000, .3, 5 >
< 80, Ande, 6400, .1, 31 >
< 80, Banda, 6200, .1, 32 >
< 80, Bates, 7300, .15, 26 >
< 80, Bernstein, 9500, .25, 14 >
< 80, Bloom, 10000, .2, 9 >
< 80, Cambrault, 7500, .2, 23 >
< 80, Cambrault, 11000, .3, 5
< 80, Doran, 7500, .3, 24 >
< 80, Errazuriz, 12000, .3, 3 >
10 rows found.

REPLACE

REPLACE substitutes a sequence of characters in a given string with another set of characters or removes the string entirely.

SQL syntax

REPLACE (String, SearchString [,ReplacementString] )

Parameters

REPLACE has the parameters:

Description

• REPLACE returns a string where every occurrence of the SearchString is replaced with ReplacementString in the source String.

• String, SearchString and ReplacementString can be any of the following data types: CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. Both TimesTen and Oracle Database data types are supported. All non-character data types, except for BLOB, are implicitly converted to a string data type.

• If String is a CHAR or VARCHAR2, the returned string is of data type VARCHAR2. If String is an NCHAR or NVARCHAR2, the returned string is of data type NVARCHAR2. For CLOB or NCLOB data types, the data type returned is the same as the data type provided in String. The character set is the same as the source String.

• If the returned string length is zero, NULL is returned for Oracle Database data types and a zero length string is returned for TimesTen data types. See Chapter 1, "Data Types" for details on all data types.

Examples

The following prints out all locations in Canada, replacing the country code of CA with Canada for easier readability.

Command> SELECT location_id, street_address,
> city, state_province, postal_code,
> REPLACE(country_id, 'CA', 'Canada') 
> FROM LOCATIONS 
> WHERE country_id LIKE 'CA';

< 1800, 147 Spadina Ave, Toronto, Ontario, M5V 2L7, Canada >
< 1900, 6092 Boxwood St, Whitehorse, Yukon, YSW 9T2, Canada >
2 rows 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, the data type returned is the same as the numeric data type of Expression1, unless Expression1 is of type TT_DECIMAL. If Expression1 is of type TT_DECIMAL and you omit Expression2,the data type returned is NUMBER with maximum precision and scale.

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

ROW_NUMBER

The ROW_NUMBER function is an analytic function that assigns a unique number to each row to which it is applied (either each row in a partition or each row returned by the query), in the ordered sequence of rows specified in OrderByClause, beginning with 1.

SQL syntax

ROW_NUMBER () OVER ( [QueryPartitionClause] OrderByClause )

Parameters

ROW_NUMBER has the parameters:

Description

• ROWNUM pseudo column returns a number indicating the order in which TimesTen selects a row from a table or a set of joined rows. In contrast, the analytic function, ROW_NUMBER, gives superior support in ordering the results of a query before assigning the number.

• By nesting a subquery, using ROW_NUMBER, inside a query that retrieves the ROW_NUMBER values for a specified range, you can find a precise subset or rows from the results of the inner query. For consistent results, the query must ensure a deterministic sort order.

• The return data type is NUMBER.

Example

Use ROW_NUMBER to return the three highest paid employees in each department. Fewer then three rows are returned for departments with fewer than three employees.

Command> SELECT FIRST 10 department_id, first_name, last_name, salary
       > FROM
       >    (SELECT department_id, first_name, last_name, salary, ROW_NUMBER()
       >       OVER (PARTITION BY department_id ORDER BY salary desc) rn
       >     FROM employees )
       > WHERE rn <= 3
       > ORDER BY department_id, salary DESC, last_name;
< 10, Jennifer, Whalen, 4400 >
< 20, Michael, Hartstein, 13000 >
< 20, Pat, Fay, 6000 >
< 30, Den, Raphaely, 11000 >
< 30, Alexander, Khoo, 3100 >
< 30, Shelli, Baida, 2900 >
< 40, Susan, Mavris, 6500 >
< 50, Adam, Fripp, 8200 >
< 50, Matthew, Weiss, 8000 >
< 50, Payam, Kaufling, 7900 >
10 rows 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. If Expression1 is a LOB, the data type returned is the same as the LOB data type provided.

• 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, NVARCHAR2, CLOB or NCLOB data types:

  • If either Expression1 or Expression2 is NULL, the result is NULL. If n is less than or equal to 0, 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, 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 five 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 five 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 backward 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. If Expression1 is a CLOB or NCLOB, the data type returned is the same as the LOB data type provided. 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, NVARCHAR2, CLOB and NCLOB types:

  • If all the characters in Expression1 are removed by the RTRIM function, 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, 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.

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;

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.

SOUNDEX

The SOUNDEX function determines a phonetic signature for a string and allows comparisons of strings based on phonetic similarity. SOUNDEX lets you compare words that are spelled differently, but sound alike in English.

SQL syntax

SOUNDEX (InputString)

Parameters

SOUNDEX has the parameters:

Description

• Converts an alpha-numeric string into a 4 character code, beginning with the first letter encountered in the string, followed by three numbers.

• The phonetic representation is defined in The Art of Computer Programming, Volume 3: Sorting and Searching, by Donald E. Knuth, as follows:

  1. Retain the first letter of the string and drop all other occurrences of the following letters: A, E, I, O, U. The treatment of the letters is case insensitive.

  2. Drop all occurrences of H, W, and Y.

  3. Assign numbers to the remaining letters (after the first) as follows:

     B, F, P, V = 1
     C, G, J, K, Q, S, X, Z = 2
     D, T = 3
     L = 4
     M, N = 5
     R = 6
     

  4. If two or more letters with the same number were adjacent in the original name (before step 1), omit all but the first.

  5. Return the first four characters of the result (padded with '0' if the result has less than four characters.)

• The function returns NULL if no soundex code could be generated for the InputString. For example, NULL is returned when the InputString contains no English letters.

• The input to output type mapping is:

  Input Type	Output Type
  VARCHAR2(x), CHAR, CLOB	VARCHAR2(4)
  TT_CHAR(x), TT_VARCHAR(x)	TT_VARCHAR(4)
  NVARCHAR2(x), NCHAR(x), NCLOB	NVARCHAR2(4)
  TT_NCHAR(x), TT_NVARCHAR(x)	TT_NVARCHAR(4)

  
  

Examples

Use SOUNDEX function to return the phonetic signature for employees with last name equal to 'Taylor'.

Command> SELECT last_name, first_name, SOUNDEX (last_name)
       > FROM employees where last_name = 'Taylor';
< Taylor, Jonathon, T460 >
< Taylor, Winston, T460 >
2 rows found.

Invoke the function again to return the phonetic signature for the string 'Tailor'. Invoke the function a third time to return the last name and first name of each employee whose last name is phonetically similar to the string 'Tailor'.

Command> SELECT SOUNDEX ('Tailor') FROM dual;
< T460 >
1 row found.

Command> SELECT last_name, first_name FROM employees WHERE SOUNDEX (last_name) = 
       > SOUNDEX ('Tailor');
< Taylor, Jonathon >
< Taylor, Winston >
2 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.

SUBSTR, SUBSTRB, SUBSTR4

Returns a string that represents a substring of a source 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}=(Source, 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 five 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 five 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.

SUM

Finds the total of all values in the argument. Null values are ignored. SUM is an aggregate function. SUM can also be an aggregate analytic function. For more details on aggregate functions, see "Aggregate functions". For more information on analytic functions, see "Analytic functions".

SQL syntax

SUM ([ALL | DISTINCT] Expression) [OVER ([AnalyticClause])]

Parameters

SUM has the parameters:

Description

• If the SUM function is computed over an empty table in which GROUP BY is not used, SUM returns NULL.

• If the SUM function is computed over an empty group or an empty grouped table (GROUP BY is used), SUM returns nothing.

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

• If you do not use the AnalyticClause in your query, then SUM acts as an aggregate function.

• If you specify DISTINCT and the AnalyticClause, then you can only specify the QueryPartitionClause. The OrderByClause and WindowingClause are not allowed.

Examples

Sum all employee salaries:

Command> SELECT SUM(salary) Total FROM employees;
 
TOTAL
< 691400 >
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 Database 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.

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;

TIMESTAMPADD

The TIMESTAMPADD function adds a specified number of intervals to a timestamp and returns the modified timestamp.

SQL syntax

TIMESTAMPADD (Interval, IntegerExpression, TimestampExpression)

Parameters

TIMESTAMPADD has the parameters:

Description

• Valid values for Interval are:

  • SQL_TSI_FRAC_SECOND

  • SQL_TSI_SECOND

  • SQL_TSI_MINUTE

  • SQL_TSI_HOUR

  • SQL_TSI_DAY

  • SQL_TSI_WEEK

  • SQL_TSI_MONTH

  • SQL_TSI_QUARTER

  • SQL_TSI_YEAR

• SQL_TSI_FRAC_SECOND is expressed in billionths of a second.

• The return type is the same as the original data type. For example, if your expression is of type TIMESTAMP, then the resulting data type is TIMESTAMP. Only positive timestamp expressions (0001-01-01) are allowed both in the query and the result. For TT_DATE and TT_TIMESTAMP, because the starting range for these data types is 1753-01-01, the timestamp expression must be equal to or greater than this date.

• If IntegerExpression or TimestampExpression is NULL, then the result is NULL.

• The function computes the total time interval as a product of the IntegerExpression and the interval and adds it to the specified TimestampExpression. Adding a year advances the timestamp by 12 months and adding a week advances the timestamp by seven days. If the IntegerExpression is negative, the specified interval is subtracted from the TimestampExpression.

• There is a possibility of precision loss depending on your use of the specified interval and timestamp expression. For example, if your interval is SQL_TSI_HOUR, and you specify 2 for IntegerExpression and TT_DATE for TimestampExpression, TimesTen treats the two hours as zero days and returns the sum of the original date plus zero days resulting in some loss of precision. If however, your IntegerExpression is 48, TimesTen treats the 48 hours as two days and returns the sum of the original date plus two days. In this case, there is no loss of precision.

• If the addition of the timestamp results in an overflow of the specified component (such as more than 60 seconds, or more than 24 hours, or more than 12 months), then the overflow is carried over to the next component. For example, if the seconds component overflows, then the minutes component is advanced.

Examples

Use the TIMESTAMPADD function to add 3 months to timestamp '2009-11-30 10:00:00'. TimesTen increments the year and adjusts the day component to accommodate the 28 days in the month of February.

Command> SELECT TIMESTAMPADD (SQL_TSI_MONTH, 3, TIMESTAMP '2010-11-30 10:00:00')
       > FROM dual;
< 2011-02-28 10:00:00 >
1 row found.

Use the TIMESTAMPADD function to add 1 second to timestamp '2010-12-31 23:59:59'. TimesTen propagates the overflow through all components of the timestamp and advances the components appropriately.

Command> SELECT TIMESTAMPADD (SQL_TSI_SECOND, 1, TIMESTAMP '2010-12-31 23:59:59')
       > FROM dual;
< 2011-01-01 00:00:00 >
1 row found.

TIMESTAMPDIFF

The TIMESTAMPDIFF function returns the total number of specified intervals between two timestamps.

SQL syntax

TIMESTAMPDIFF (Interval, TimestampExpression1, TimestampExpression2)

Parameters

TIMESTAMPDIFF has the parameters:

Description

• Valid values for Interval are:

  • SQL_TSI_FRAC_SECOND

  • SQL_TSI_SECOND

  • SQL_TSI_MINUTE

  • SQL_TSI_HOUR

  • SQL_TSI_DAY

  • SQL_TSI_WEEK

  • SQL_TSI_MONTH

  • SQL_TSI_QUARTER

  • SQL_TSI_YEAR

• SQL_TSI_FRAC_SECOND is expressed in billionths of a second.

• Interval determines the units in which the difference in timestamps is returned. For example, if you specify SQL_TSI_YEAR, the difference in timestamps is returned in years.

• TimesTen returns the result as the difference between TimestampExpression2 minus (-) TimestampExpression1. The return type is TT_BIGINT.

• Only positive timestamp expressions (0001-01-01) are allowed. For TT_DATE and TT_TIMESTAMP, because the starting range for these data types is 1753-01-01, the timestamp expression must be equal to or greater than this date.

• If TimestampExpression1 or TimestampExpression2 is NULL, then the result is NULL.

• If either timestamp expression is a date value and Interval specifies fractional seconds, seconds, minutes, or hours, the time portion of the timestamp is set to 0 before TimesTen calculates the difference between the timestamps.

• The function first expresses each of the timestamps in units of the specified Interval by converting the higher order interval type to the specified interval type. For example, TimesTen converts years to months if the specified interval is months. Thus, one year is 12 months, one week is seven days, and so on. To find the number of days between two timestamps, the exact number of days is computed. Since months vary in the number of days, TimesTen does not make an assumption about the number of days in a month.

• The function increments the specified interval whenever fractional intervals cross an interval boundary. For example, the difference in years between 2009-12-31 and 2010-01-01 is one year because the fractional year represents a crossing from one year to the next (2009 to 2010). However, the difference between 2010-01-01 and 2010-12-31 is zero years because the fractional interval does not cross a boundary. It falls within the year 2010.

• The function calculates the difference in weeks by first calculating the difference in days and then divides the result by seven before rounding. TimesTen assumes a week starts on a Sunday. Therefore the difference in weeks between 2010-10-21 (a Thursday) and 2010-10-25 (the following Monday) results in a value of one week. The difference in the same dates, if Tuesday denoted the start of the week, would result in zero weeks.

Examples

Use the TIMESTAMPDIFF function to calculate the difference in days between dates 2008-02-01 and 2008-03-01. Because 2008 is a leap year, the result is 29 days. The calculation is precise with no assumption of a 30-day month.

Command> SELECT TIMESTAMPDIFF (SQL_TSI_DAY, DATE '2008-02-01',
       > DATE '2008-03-01') FROM dual;
< 29 >
1 row found.

Use the TIMESTAMPDIFF function to calculate the difference in months between dates 2009-02-01 and 2009-03-01. Because there is a crossing of the interval month boundary, the function returns 1. In the second example, because days is specified for the interval, the result is 28.

Command> SELECT TIMESTAMPDIFF (SQL_TSI_MONTH, DATE '2009-02-01',
       > DATE '2009-03-01') FROM dual;
< 1 >
1 row found.

Command> SELECT TIMESTAMPDIFF (SQL_TSI_DAY, DATE '2009-02-01',
       > DATE '2009-03-01') FROM dual;
< 28 >
1 row found.

Use the TIMESTAMPDIFF function to calculate the difference in months between dates 2009-02-01 and 2009-02-29. Because there is not a crossing of the interval month boundary, the function returns 0.

Command> SELECT TIMESTAMPDIFF (SQL_TSI_MONTH, DATE '2009-02-01',
       > DATE '2009-02-28') FROM dual;
< 0 >
1 row found.

Use the TIMESTAMPDIFF function to illustrate the time difference in fractional seconds between mixed types. The time difference of one hour is returned in nanoseconds (unit for fractional seconds). The time element of the data type is set to 00:00:00.

Command> SELECT TIMESTAMPDIFF (SQL_TSI_FRAC_SECOND,
       > TT_TIMESTAMP '2009-12-31 01:00:00.00', DATE '2009-12-31') FROM dual;
< -3600000000000 >
1 row found.

TO_BLOB

The TO_BLOB function converts VARBINARY or BINARY to a BLOB:

SQL syntax

TO_BLOB ( ValidDataType )

Parameters

TO_BLOB has the parameters:

Examples

The following example creates a table with a BINARY and a VARBINARY columns. The TO_BLOB function is used on the values of these columns to convert the BINARY and VARBINARY data to a BLOB.

Command> CREATE TABLE bvar 
 > (col1 BINARY (10), col2 VARBINARY (10));

Command> INSERT INTO bvar (col1, col2)
 > VALUES (0x4D7953514C, 0x39274D);
1 row inserted.

Command> SELECT * FROM bvar;
 < 4D7953514C0000000000, 39274D >
1 row found.

Command> SELECT TO_BLOB(col1), TO_BLOB(col2) 
 > FROM bvar;
< 4D7953514C0000000000, 39274D >
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_CLOB

The TO_CLOB function converts one of the following values to a CLOB:

• Character value contained in one of the following data types: CHAR, VARCHAR2, NVARCHAR2, TT_VARCHAR, TT_NVARCHAR, or NCLOB.

• Datetime value contained in a DATE or TIMESTAMP data type.

• Number value contained in a NUMBER, BINARY_FLOAT, or BINARY_DOUBLE data type.

SQL syntax

TO_CLOB ( ValidDataType )

Parameters

TO_CLOB has the parameters:

Description

The TO_CLOB function will not operate on values contained in INTERVAL or TIMESTAMP with TIMEZONE data types.

Examples

The following example uses the TO_CLOB function to convert a string.

Command> DESCRIBE clob_content;
 
Table USER1.CLOB_CONTENT:
  Columns:
   *ID                              NUMBER (38) NOT NULL
    CLOB_COLUMN                     CLOB NOT NULL
 
1 table found.
(primary key columns are indicated with *)

Command> INSERT INTO clob_content (id, clob_column) 
 > VALUES (3, EMPTY_CLOB());
1 row inserted.

Command> UPDATE clob_content 
 > SET clob_column = TO_CLOB('Demonstration of the TO_CLOB function.') 
 > WHERE id = 3;
1 row updated.

TO_DATE

The TO_DATE function converts a CHAR, VARCHAR2, CLOB, or NCLOB 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 AM') FROM DUAL;
< DECEMBER  23, 1997 10:56-20 AM >
1 row found.

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

TO_LOB

The TO_LOB function converts supplied TT_VARCHAR and VARCHAR2 data types to a CLOB and VARBINARY data types to a BLOB.

SQL syntax

TO_LOB ( ValidDataType )

Parameters

TO_LOB has the parameters:

Description

You can use this function only on a TT_VARCHAR, VARCHAR2, or VARBINARY column, and only with the CREATE TABLE AS SELECT or INSERT...SELECT statements on tables with a defined LOB column.

Examples

The following example shows how to use the TO_LOB function within the INSERT...SELECT statement on a table with a LOB column.

Command> CREATE TABLE clb(c CLOB);
Command> CREATE TABLE vc (v VARCHAR2(2000));
Command> INSERT INTO vc(v) 
 > VALUES ('Showing the functionality of the TO_LOB function');
1 row inserted.

Command> INSERT INTO clb 
SELECT TO_LOB(v) FROM vc; 
1 row inserted.

Command> SELECT * FROM clb;
< Showing the functionality of the TO_LOB function >
1 row found.

Because of the restriction mentioned above, you cannot use the TO_LOB function in all cases where you can use the TO_CLOB or TO_BLOB functions. The following example demonstrates the error you receive when you try to use the TO_LOB function in this manner:

Command> SELECT TO_LOB(col1) 
 > FROM bvar;
 2610: Operand data type 'BINARY' invalid for operator 
 'TO_LOB' in expr ( TO_LOB( BVAR.COL1 ))
The command failed. 

TO_NCLOB

The TO_NCLOB function converts one of the following values to a NCLOB:

• Character value contained in one of the following data types: CHAR, VARCHAR2, NVARCHAR2, TT_VARCHAR, TT_NVARCHAR, or NCLOB.

• Datetime value contained in a DATE or TIMESTAMP data type.

• Number value contained in a NUMBER, BINARY_FLOAT, or BINARY_DOUBLE data type.

SQL syntax

TO_NCLOB ( ValidDataType )

Parameters

TO_NCLOB has the parameters:

Examples

The following converts the data in the VARCHAR2 job_title column to be of data type NCLOB.

Command> SELECT TO_NCLOB(job_title) FROM jobs;

< Public Accountant >
< Accounting Manager >
< Administration Assistant >
< President >
< Administration Vice President >
< Accountant >
< Finance Manager >
< Human Resources Representative >
< Programmer >
< Marketing Manager >
< Marketing Representative >
< Public Relations Representative >
< Purchasing Clerk >
< Purchasing Manager >
< Sales Manager >
< Sales Representative >
< Shipping Clerk >
< Stock Clerk >
< Stock Manager >
19 rows found.

TO_NUMBER

Converts an expression 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 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. In this case, Trim_character defaults to a blank.

  TRIM ( LEADING|TRAILING|BOTH FROM Expression )
  

• You can specify the Trim_character without one of the TRIM qualifiers, which removes both leading and trailing instances of Trim_character from Expression.

  TRIM (Trim_character FROM Expression )
  

• You can specify the Expression without a qualifier or a Trim_character, which results in leading and trailing blank spaces removed from Expression.

  TRIM ( Expression )
  

Parameters

TRIM has the parameters:

Description

• 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. If Expression is of type CLOB, the data type returned is CLOB. If Expression is of type NCLOB, the data type returned is NCLOB. 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, NVARCHAR2, CLOB and NCLOB types:

  • If all the characters in Expression are removed by the TRIM function, 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, 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 ttXactAdmin 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; 

TTGRIDMEMBERID

When executed within a global query, gives the member ID in the cache grid of the owning member for each returned row.

SQL syntax

TTGRIDMEMBERID()

Parameters

TTGRIDMEMBERID() has no parameters.

Description

TTGRIDMEMBERID(), when executed within a global query, returns a TT_INTEGER with the member ID of the member where each returned row is located in the cache grid. If not executed within a global query, the current member ID is returned. NULL is returned if the member's node is not attached to a grid.

Autocommit should be set to OFF.

See "Obtaining information about the location of data in the cache grid" in Oracle In-Memory Database Cache User's Guide for more information on this function.

Examples

The following example shows autocommit set to OFF and sets the GlobalProcessing optimizer hint to 1, which makes the statements following part of a global query. The SELECT statement retrieves the employee number and member ID of the member where the employee data resides. The following output shows that the employee rows exist on members 2 and 4.

Commmand> AUTOCOMMIT OFF;
Commmand> CALL ttOptSetFlag('GlobalProcessing', 1);
Commmand> SELECT employee_id, TTGRIDMEMBERID() FROM employees;
< 7900, 2 >
< 7902, 4 >
2 rows found.

The following example uses TTGRIDMEMBERID() in an ORDER BY clause.

Commmand> AUTOCOMMIT OFF;
Commmand> CALL ttOptSetFlag('GlobalProcessing', 1);
Command> SELECT employee_id, TTGRIDMEMBERID() FROM employees 
 ORDER BY TTGRIDMEMBERID();
< 7900, 2 >
< 7902, 4 >
2 rows found.
 

The following example uses TTGRIDMEMBERID() in a WHERE clause:

Commmand> AUTOCOMMIT OFF;
Commmand> CALL ttOptSetFlag('GlobalProcessing', 1);
Command> SELECT employee_id, TTGRIDMEMBERID() FROM employees 
 WHERE TTGRIDMEMBERID()=2;
< 7900, 2 >
1 row found.

The following example executes TTGRIDMEMBERID() as a local query to retrieve the local member ID, which is 1.

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

TTGRIDNODENAME

When executed within a global query, returns the name of the node in a cache grid on which the data is located.

SQL syntax

TTGRIDNODENAME()

Parameters

TTGRIDNODENAME() has no parameters.

Description

TTGRIDNODENAME() returns a TT_VARCHAR(64) with the node name of the node on which each returned row is located, which shows the location of data in a cache grid. NULL is returned if the node is not attached to a grid.

Autocommit should be set to OFF.

See "Obtaining information about the location of data in the cache grid" in Oracle In-Memory Database Cache User's Guide for more information.

Examples

The following example shows autocommit set to OFF and sets the GlobalProcessing optimizer hint to 1, which makes the statements following part of a global query. The SELECT statement retrieves the employee number and name of the node where the employee data resides. The following output shows that the employee rows exist on members 2 and 4.

Commmand> AUTOCOMMIT OFF;
Commmand> CALL ttOptSetFlag('GlobalProcessing', 1);
Commmand> SELECT employee_id, TTGRIDNODENAME() FROM employees;
< 7900, MYGRID_member2 >
< 7902, MYGRID_member4 >
2 rows found.

TTGRIDUSERASSIGNEDNAME

Within a global query, returns the user-assigned name of the node in a cache grid on which the data is located.

SQL syntax

TTGRIDUSERASSIGNEDNAME()

Parameters

TTGRIDUSERASSIGNEDNAME() has no parameters.

Description

TTGRIDUSERASSIGNEDNAME() returns a TT_VARCHAR(30) with the node name assigned by the user to the node of the grid on which the data is located. NULL is returned if the node is not attached to a grid.

Autocommit should be set to OFF.

See "Obtaining information about the location of data in the cache grid" in Oracle In-Memory Database Cache User's Guide for more information.

Examples

The following example includes a cache grid whose members have user-assigned names alone1, alone2, and an active standby pair on nodes cacheact and cachestand. The standby database has the same data as the active database, but the query does not return data from the standby database.

The following example retrieves employee_id and the user-assigned node name with TTGRIDUSERASSIGNEDNAME() from the employees table from the grid members. The returned rows show which grid node owns each row of the cache instance.

Commmand> AUTOCOMMIT OFF;
Commmand> CALL ttOptSetFlag('GlobalProcessing', 1);
Commmand> SELECT employee_id, TTGRIDUSERASSIGNEDNAME() FROM employees;
Commmand> COMMIT;
< 100, alone1>
< 101, alone2>
< 102, cacheact>
< 103, alone1>
< 104, cacheact>
...

Subsequent queries can access the appropriate node without changing the ownership of the data. For example, execute this query on grid node cacheact, including TTGRIDUSERASSIGNEDNAME() to verify that cacheact is the node where the data is located:

Commmand> AUTOCOMMIT OFF;
Commmand> CALL ttOptSetFlag('GlobalProcessing', 1);
Commmand> SELECT employee_id, last_name, hire_date, TTGRIDUSERASSIGNEDNAME() 
 FROM employees WHERE employee_id=104;
< 104, Ernst, cacheact, 1991-05-21 00:00:00 >

The following example retrieves the employee number and user-assigned name for the node on which the employee data exists and orders the returned data by the user-assigned name:

Commmand> AUTOCOMMIT OFF;
Commmand> CALL ttOptSetFlag('GlobalProcessing', 1);
Commmand> SELECT employee_id, TTGRIDUSERASSIGNEDNAME() FROM employees ORDER BY
 TTGRIDUSERASSIGNEDNAME() ASC;
< 7900, member2 >
< 7902, member4 >
2 rows found.

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

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;