Oracle® Database SQL Reference 10g Release 1 (10.1) Part Number B10759-01 |
|
|
View PDF |
NUMTOYMINTERVAL
converts number n
to an INTERVAL
YEAR
TO
MONTH
literal. The argument n
can be any NUMBER
value or an expression that can be implicitly converted to a NUMBER
value. The argument interval_unit
can be of CHAR
, VARCHAR2
, NCHAR
, or NVARCHAR2
datatype. The value for interval_unit
specifies the unit of n
and must resolve to one of the following string values:
'YEAR
'
'MONTH
'
interval_unit
is case insensitive. Leading and trailing values within the parentheses are ignored. By default, the precision of the return is 9.
The following example calculates, for each employee, the total salary of employees hired in the past one year from his or her hire date.
SELECT last_name, hire_date, salary, SUM(salary) OVER (ORDER BY hire_date RANGE NUMTOYMINTERVAL(1,'year') PRECEDING) AS t_sal FROM employees; LAST_NAME HIRE_DATE SALARY T_SAL ------------------------- --------- ---------- ---------- King 17-JUN-87 24000 24000 Whalen 17-SEP-87 4400 28400 Kochhar 21-SEP-89 17000 17000 . . . Markle 08-MAR-00 2200 112400 Ande 24-MAR-00 6400 106500 Banda 21-APR-00 6200 109400 Kumar 21-APR-00 6100 109400