I am confuse, can we write SELECT statement in FROM clause and if yes why can it be.
SELECT v.employee_id, v.last_name, v.lev
FROM
(SELECT employee_id, last_name, LEVEL lev
FROM employees v
START WITH employee_id = 100
CONNECT BY PRIOR employee_id = manager_id) v
WHERE (v.employee_id, v.lev) IN
(SELECT employee_id, 2 FROM employees);
The answer is yes, you can use. The select clause in the from will act as a inline view(consider it as a temporary table that databse creates to hold the results). For example:
SELECT sdt sdat
FROM (SELECT SYSDATE sdt FROM dual);
In the above query, SELECT SYSDATE sdt FROM dual is executed first, and output would be like:
sdt
---
08-Dec-2016 16:20:56
Then, using this as a temp table(which is called an inline view in such cases), oracle will execute your outer select on this data. Hence SELECT sdt FROM... executes, giving the final output as:
sdat
----
08-Dec-2016 16:20:56
I think you are looking for a recursive cte in sql server which would be something like.....
WITH X (employee_id, last_name, lev )
AS (
SELECT employee_id, last_name, 0 AS lev
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.employee_id, e.last_name , lev + 1
FROM employees e
INNER JOIN x ON x.employee_id = e.manager_id
)
SELECT v.employee_id, v.last_name, v.lev
FROM X
WHERE lev = 2
SQL Server unless the OP has changed his mind.WHERE clause.