August 2, 2005 at 11:44 pm
Hi all,
I have seen an example in Oracle to get heirarchial query as under without using any loop.. i would like to know whether the same task can be done in sql with out using any loop ?
SELECT E.emplevel, SUBSTR(E.ename,1,15) "ENAME", E.empno, dept.deptno, dept.dname
FROM dept, (SELECT level emplevel, LPAD(' ',2*level-2)||ename ename, empno, mgr, deptno
FROM emp
CONNECT BY PRIOR empno = mgr
START WITH empno = 7839) E
WHERE E.deptno = dept.deptno
/
EMPLEVEL ENAME EMPNO DEPTNO DNAME
---------- --------------- ---------- ---------- --------------
1 KING 7839 10 ACCOUNTING
2 CLARK 7782 10 ACCOUNTING
3 MILLER 7934 10 ACCOUNTING
2 JONES 7566 20 RESEARCH
3 SCOTT 7788 20 RESEARCH
4 ADAMS 7876 20 RESEARCH
3 FORD 7902 20 RESEARCH
4 SMITH 7369 20 RESEARCH
2 BLAKE 7698 30 SALES
3 ALLEN 7499 30 SALES
3 WARD 7521 30 SALES
3 MARTIN 7654 30 SALES
3 TURNER 7844 30 SALES
3 JAMES 7900 30 SALES
Thanks ,
NSR
August 4, 2005 at 9:19 am
It can be done with a common table expression (CTE), one of the new features of SQL Server 2005. The syntax is a bit different than Oracle, but it is a similar concept. I have copied the examples right out of Sql Server Help (Books online) for SQL 2005:
USE AdventureWorks;
GO
WITH DirectReports(EmployeeID, NewVacationHours, EmployeeLevel)
AS
(SELECT e.EmployeeID, e.VacationHours, 1
FROM HumanResources.Employee AS e
WHERE e.ManagerID = 12
UNION ALL
SELECT e.EmployeeID, e.VacationHours, EmployeeLevel + 1
FROM HumanResources.Employee as e
JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
UPDATE HumanResources.Employee
SET VacationHours = VacationHours * 1.25
FROM HumanResources.Employee AS e
JOIN DirectReports AS d ON e.EmployeeID = d.EmployeeID;
GO
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply