Alternate to Oracle 'Connect By Prior'

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Alternate to Oracle 'Connect By Prior'

Author

Topic

sqlmaddy
Starting Member

4 Posts

Posted - 2010-08-18 : 08:23:07

Hi friends, I have the following query in Oracle.

SELECT Lpad(ename,Length(ename) + LEVEL * 10 - 10,'-')
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr

Can any one please translate the above query into SQL Server. Since last one week I am struggling for the output. Please help me. Its very urgent.

Thanks,
SQL Aspirant.

SQL

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-18 : 11:15:11

Read about Commom Table Expression in SQL Server help file

Madhivanan

Failing to plan is Planning to fail

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-08-18 : 17:13:48

>> Can any one please translate the above query into SQL Server. Since last one week I am struggling for the output. <<

You can do this with a complicated recursive CTE. Of course the output is not even in First Normal Form (1NF). I would consider doing this with the nested sets model for trees (Google it) instead of an adjacency list model like you have.

Get a copy of TREES & HIERARCHIES IN SQL for more help.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

sqlmaddy
Starting Member

4 Posts

Posted - 2010-08-19 : 00:45:48

Hi,
I want to display the data in hierarchical fashion using SQL Server. I got the solution in Oracle, but I am using SQL Server. Please help me the query.

Cheers,
Maddy.

SQL

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-08-19 : 02:34:31

Recursive queries are not exactly my strong side but I think this is what you need except for the level-part:

WITH CTE AS (
  SELECT empno, mgr, ename
  FROM emp
  WHERE mgr IS NULL 

  UNION ALL

  SELECT e.empno, e.mgr, e.ename
  FROM emp e
    INNER JOIN CTE ecte 
      ON ecte.empno = e.mgr
)
SELECT * FROM CTE

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-08-19 : 02:40:32

I *think* this is more or less exactly as you need it:

WITH CTE AS (
  SELECT empno, mgr, ename, 0 AS [Level]
  FROM emp
  WHERE mgr IS NULL 

  UNION ALL

  SELECT e.empno, e.mgr, e.ename, [Level] + 1
  FROM emp e
    INNER JOIN CTE ecte 
      ON ecte.empno = e.mgr
)
SELECT *, REPLICATE('-', 10 * [Level])
FROM CTE

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

sqlmaddy
Starting Member

4 Posts

Posted - 2010-08-19 : 02:53:57

Dear Lumbago,
I thank you for your response. I will provide you the sample table, data, and stored procedure for this. In this procedure, I am getting output in a message format. Could you please send me the output in a table format as I need to use the table in Front End application. Please do this help as I am not getting since last 3 days.

Sample Data
===============

CREATE TABLE dbo.Emp
(
EmpID int PRIMARY KEY,
EmpName varchar(30),
MgrID int FOREIGN KEY REFERENCES Emp(EmpID)
)
GO

INSERT dbo.Emp SELECT 1, 'President', NULL
INSERT dbo.Emp SELECT 2, 'Vice President', 1
INSERT dbo.Emp SELECT 3, 'CEO', 2
INSERT dbo.Emp SELECT 4, 'CTO', 2
INSERT dbo.Emp SELECT 5, 'Group Project Manager', 4
INSERT dbo.Emp SELECT 6, 'Project Manager 1', 5
INSERT dbo.Emp SELECT 7, 'Project Manager 2', 5
INSERT dbo.Emp SELECT 8, 'Team Leader 1', 6
INSERT dbo.Emp SELECT 9, 'Software Engineer 1', 8
INSERT dbo.Emp SELECT 10, 'Software Engineer 2', 8
INSERT dbo.Emp SELECT 11, 'Test Lead 1', 6
INSERT dbo.Emp SELECT 12, 'Tester 1', 11
INSERT dbo.Emp SELECT 13, 'Tester 2', 11
INSERT dbo.Emp SELECT 14, 'Team Leader 2', 7
INSERT dbo.Emp SELECT 15, 'Software Engineer 3', 14
INSERT dbo.Emp SELECT 16, 'Software Engineer 4', 14
INSERT dbo.Emp SELECT 17, 'Test Lead 2', 7
INSERT dbo.Emp SELECT 18, 'Tester 3', 17
INSERT dbo.Emp SELECT 19, 'Tester 4', 17
INSERT dbo.Emp SELECT 20, 'Tester 5', 17

CREATE PROC dbo.ShowHierarchy
(
@Root int
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @EmpID int, @EmpName varchar(30)

SET @EmpName = (SELECT EmpName FROM dbo.Emp WHERE EmpID = @Root)
PRINT REPLICATE('-', @@NESTLEVEL * 1) + @EmpName

SET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE MgrID = @Root)

WHILE @EmpID IS NOT NULL
BEGIN
EXEC dbo.ShowHierarchy @EmpID
SET @EmpID = (SELECT MIN(EmpID) FROM dbo.Emp WHERE MgrID = @Root AND EmpID > @EmpID)
END
END

--EXEC ShowHierarchy 1

Please help me. Early response would be appreciated.

Cheers,
Maddy.

SQL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-19 : 03:35:04

[code]DECLARE @Sample TABLE
(
EmpID INT,
EmpName VARCHAR(30),
MgrID INT
)

INSERT @Sample
SELECT 1, 'President', NULL UNION ALL
SELECT 2, 'Vice President', 1 UNION ALL
SELECT 3, 'CEO', 2 UNION ALL
SELECT 4, 'CTO', 2 UNION ALL
SELECT 5, 'Group Project Manager', 4 UNION ALL
SELECT 6, 'Project Manager 1', 5 UNION ALL
SELECT 7, 'Project Manager 2', 5 UNION ALL
SELECT 8, 'Team Leader 1', 6 UNION ALL
SELECT 9, 'Software Engineer 1', 8 UNION ALL
SELECT 10, 'Software Engineer 2', 8 UNION ALL
SELECT 11, 'Test Lead 1', 6 UNION ALL
SELECT 12, 'Tester 1', 11 UNION ALL
SELECT 13, 'Tester 2', 11 UNION ALL
SELECT 14, 'Team Leader 2', 7 UNION ALL
SELECT 15, 'Software Engineer 3', 14 UNION ALL
SELECT 16, 'Software Engineer 4', 14 UNION ALL
SELECT 17, 'Test Lead 2', 7 UNION ALL
SELECT 18, 'Tester 3', 17 UNION ALL
SELECT 19, 'Tester 4', 17 UNION ALL
SELECT 20, 'Tester 5', 17

DECLARE @Root INT

-- Get a random number between 1 and 20
-- This is in reality your user supplied parameter of the stored procedure
SET @Root = 1 + ABS(CHECKSUM(NEWID())) % 20

;WITH cteManagers(EmpID, EmpName, MgrID, Indentation, MgrPath)
AS (
SELECT EmpID,
EmpName,
MgrID,
0 AS Indentation,
CAST(EmpID AS VARCHAR(MAX)) AS MgrPath
FROM @Sample
WHERE EmpID = @Root

UNION ALL

SELECT s.EmpID,
s.EmpName,
s.MgrID,
c.Indentation + 1 AS Indentation,
c.MgrPath + '\' + CAST(s.EmpID AS VARCHAR(MAX)) AS MgrPath
FROM @Sample AS s
INNER JOIN cteManagers AS c ON c.EmpID = s.MgrID
)
SELECT EmpID,
EmpName,
MgrID,
Indentation,
MgrPath
FROM cteManagers
ORDER BY MgrPath[/code]

N 56°04'39.26"
E 12°55'05.63"

sqlmaddy
Starting Member

4 Posts

Posted - 2010-08-20 : 07:03:32

Thanks dude. I got the answer. I am taking complete data into dataset and displaying in the application. Thanks for your reply..

Cheers,

SQL

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-20 : 07:14:55

So my time spent on your problem was a complete waste of time?

N 56°04'39.26"
E 12°55'05.63"

Sachin.Nand

2937 Posts