Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
Functions, 65 of 121
Note: This function is restricted to use with analytic functions. It accepts only numbers as arguments, and returns interval literals. See "Analytic Functions" and "Interval". |
NUMTOYMINTERVAL
converts number n to an INTERVAL
YEAR
TO
MONTH
literal. n can be a number or an expression resolving to a number. The value for char_expr specifies the unit of n, and must resolve to one of the following string values:
char_expr is case-insensitive. Leading and trailing values within the parentheses are ignored. By default, 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/her hiredate.
SELECT ename, hiredate, sal, SUM(sal) OVER (ORDER BY hiredate
RANGE NUMTOYMINTERVAL(1,'year') PRECEDING) AS t_sal
FROM emp;
ENAME HIREDATE SAL T_SAL
---------- --------- ---------- ----------
SMITH 17-DEC-80 800 800
ALLEN 20-FEB-81 1600 2400
WARD 22-FEB-81 1250 3650
JONES 02-APR-81 2975 6625
BLAKE 01-MAY-81 2850 9475
CLARK 09-JUN-81 2450 11925
TURNER 08-SEP-81 1500 13425
MARTIN 28-SEP-81 1250 14675
KING 17-NOV-81 5000 19675
JAMES 03-DEC-81 950 23625
FORD 03-DEC-81 3000 23625
MILLER 23-JAN-82 1300 24125
SCOTT 19-APR-87 3000 3000
ADAMS 23-MAY-87 1100 4100
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|