Infinte Loop Excution

Question

Post reply

Infinte Loop Excution

ranganathanmca

SSC Eights!

Points: 920
More actions
March 10, 2011 at 12:47 am

#248027

Hi Guys.,
Table structure :
create table tbemployee(
empid varchar(1200,
Name varchar(220),
supervisor_id varchar(120)
)
we need get the employee levels
Example:
--1
---2
----3
-----4
Like this we are tried some query but it's loop continually
Query :
WITH Managers AS
(
--initialization
SELECT EmpID,supervisor_ID
FROM tblEmployee
WHERE supervisor_ID = '286593'
UNION ALL
--recursive execution
SELECT e.empid,e.supervisor_ID
FROM tblEmployee e INNER JOIN Managers m
ON e.supervisor_ID = m.empid
)
select * from managers
please guide us to reslove this issue thanks for advance
Thanks
Ranganathan Palanisamy

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ranganathanmca SSC Eights! Points: 920 More actions · Answer 1

Hi guys i got solution please below query

WITH Managers AS

(

--initialization

SELECT EmpID,supervisor_ID

FROM tblEmployee

WHERE empid = '1234'

UNION ALL

--recursive execution

SELECT e.empid,e.supervisor_ID

FROM tblEmployee e INNER JOIN Managers m

ON e.supervisor_ID = m.empid

WHERE e.empid <> '1234'

)

select * from managers

Thanks

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

ranganathanmca (3/10/2011)
Hi Guys.,
Table structure :
create table tbemployee(
empid varchar(1200,
Name varchar(220),
supervisor_id varchar(120)
)
we need get the employee levels
Example:
--1
---2
----3
-----4
Like this we are tried some query but it's loop continually
Query :
WITH Managers AS
(
--initialization
SELECT EmpID,supervisor_ID
FROM tblEmployee
WHERE supervisor_ID = '286593'
UNION ALL
--recursive execution
SELECT e.empid,e.supervisor_ID
FROM tblEmployee e INNER JOIN Managers m
ON e.supervisor_ID = m.empid
)
select * from managers
please guide us to reslove this issue thanks for advance
Thanks
Ranganathan Palanisamy

Does the EmpID column have a "UNIQUE" constraint on it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 3

Jeff

A unique contraint would elimate the chance that there are 2 or more records with the same EmpId.

However it could also be a circular reference in the Employee,Supervisor chain, eg

EmpId,SupId,Name

10,4,Harry

4,3,Dick

3,10,Tom

This would give an infinte loop, if the Unique constraint is of then its likely to be this scenario

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

Jason-299789 (3/11/2011)
Jeff
A unique contraint would elimate the chance that there are 2 or more records with the same EmpId.

Exactly!. And THAT is precisely what is required for a proper "Adjacency List" as a DAG "Directed Acyclic Graph" which is typical of the requirements of ORG charts. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)