Oracle9i SQL Reference Release 1 (9.0.1) Part Number A90125-01 |
|
Functions, 58 of 166
lead::=
lead
LEAD
is an analytic function. It provides access to more than one row of a table at the same time without a self-join. Given a series of rows returned from a query and a position of the cursor, LEAD
provides access to a row at a given physical offset beyond that position.
If you do not specify offset, its default is 1. The optional default value is returned if the offset goes beyond the scope of the table. If you do not specify default, its default value is null.
You cannot use LEAD
or any other analytic function for value_expr. That is, you can use other built-in function expressions for value_expr, but you cannot nest analytic functions.
The following example provides, for each employee in the employees
table, the hiredate of the employee hired just after:
SELECT last_name, hire_date, LEAD(hire_date, 1) OVER (ORDER BY hire_date) AS "NextHired" FROM employees WHERE department_id = 30; LAST_NAME HIRE_DATE NextHired ------------------------- --------- --------- Raphaely 07-DEC-94 18-MAY-95 Khoo 18-MAY-95 24-JUL-97 Tobias 24-JUL-97 24-DEC-97 Baida 24-DEC-97 15-NOV-98 Himuro 15-NOV-98 10-AUG-99 Colmenares 10-AUG-99
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|