Infinte Loop Excution - SQL Server Forums

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

Infinte Loop Excution

Author

Topic

ranganathanmca
Starting Member

15 Posts

Posted - 2011-03-10 : 02:49:07

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

ranganathan palanisamy

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-03-10 : 10:22:46

Are you sure?

insert into tbemployee
select 1,'t', null union all
select 2,'t', 1 union all
select 3,'t', 1 union all
select 4,'t', 2 union all
select 5,'t', 3 union all
select 6,'t', 4

WITH Managers AS
(
--initialization
SELECT EmpID,supervisor_ID
FROM tbEmployee
WHERE supervisor_ID = '2'
UNION ALL
--recursive execution
SELECT e.empid,e.supervisor_ID
FROM tbEmployee e INNER JOIN Managers m
ON e.supervisor_ID = m.empid
)
select * from managers

Madhivanan

Failing to plan is Planning to fail

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-10 : 11:59:32

Please post real DDL. Learn to use ISO-11179 rules for the data element emp_names, avoid needless dialect and use ISO-8601 temporal formats, codes and so forth. People cannot read your mind, so post your code and clear specs if you really want help.

Why did you put a “tb-” prefix on a table name? That is BASIC, not proper data modeling or good SQL. The table models a set it is Personnel and not one employee. A supervisor is an employee like you said with a singular name. Likewise “name” is too vague; name of what?

Why did you use such insanely long VARCHAR(n) data types? Do you know anyone with a 220 character name? How does he fill out a form or write a letter? Is this what you meant to post, if you knew RDBMS and data modeling better?

CREATE TABLE Personnel
(emp_id CHAR(10) NOT NULL PRIMARY KEY,
emp_name VARCHAR(35) NOT NULL,
superior_emp_id VARCHAR(10) -– null means root
);

This is called an adjacency list model for a tree. You need to add several triggers that prevent cycles and orphans in the tree. They are complicated and make the code run slowly. But if you don't do them, you get infinite loops – as you found out the hard way.

Since SQL is a set oriented language, this is a better model than the usual adjacency list approach you see in most text books. Let us define a simple Personnel table like this.

CREATE TABLE Personnel
(emp_name CHAR(10) NOT NULL PRIMARY KEY,
lft INTEGER NOT NULL UNIQUE CHECK (lft > 0),
rgt INTEGER NOT NULL UNIQUE CHECK (rgt > 1),
CONSTRAINT order_okay CHECK (lft < rgt));

Personnel
emp_name lft rgt
======================
'Albert' 1 12
'Bert' 2 3
'Chuck' 4 11
'Donna' 5 6
'Eddie' 7 8
'Fred' 9 10

The (lft, rgt) pairs are like tags in a mark-up language, or parens in algebra, BEGIN-END blocks in Algol-family programming languages, etc. -- they bracket a sub-set. This is a set-oriented approach to trees in a set-oriented language.

The organizational chart would look like this as a directed graph:

Albert (1, 12)
/ / Bert (2, 3) Chuck (4, 11)
/ | / | / | / | Donna (5, 6) Eddie (7, 8) Fred (9, 10)

The adjacency list table is denormalized in several ways. We are modeling both the Personnel and the Organizational chart in one table. But for the sake of saving space, pretend that the names are job titles and that we have another table which describes the Personnel that hold those positions.

Another problem with the adjacency list model is that the boss_emp_name and employee columns are the same kind of thing (i.e. identifiers of personnel), and therefore should be shown in only one column in a normalized table. To prove that this is not normalized, assume that "Chuck" changes his name to "Charles"; you have to change his name in both columns and several places. The defining characteristic of a normalized table is that you have one fact, one place, one time.

The final problem is that the adjacency list model does not model subordination. Authority flows downhill in a hierarchy, but If I fire Chuck, I disconnect all of his subordinates from Albert. There are situations (i.e. water pipes) where this is true, but that is not the expected situation in this case.

To show a tree as nested sets, replace the nodes with ovals, and then nest subordinate ovals inside each other. The root will be the largest oval and will contain every other emp_name. The leaf nodes will be the innermost ovals with nothing else inside them and the nesting will show the hierarchical relationship. The (lft, rgt) columns (I cannot use the reserved words LEFT and RIGHT in SQL) are what show the nesting. This is like XML, HTML or parentheses.

At this point, the boss_emp_name column is both redundant and denormalized, so it can be dropped. Also, note that the tree structure can be kept in one table and all the information about a emp_name can be put in a second table and they can be joined on employee number for queries.

To convert the graph into a nested sets model think of a little worm crawling along the tree. The worm starts at the top, the root, makes a complete trip around the tree. When he comes to a emp_name, he puts a number in the cell on the side that he is visiting and increments his counter. Each emp_name will get two numbers, one of the right side and one for the left. Computer Science majors will recognize this as a modified preorder tree traversal algorithm. Finally, drop the unneeded Personnel.boss_emp_name column which used to represent the edges of a graph.

This has some predictable results that we can use for building queries. The root is always (left = 1, right = 2 * (SELECT COUNT(*) FROM TreeTable)); leaf nodes always have (left + 1 = right); subtrees are defined by the BETWEEN predicate; etc. Here are two common queries which can be used to build others:

1. An employee and all their Supervisors, no matter how deep the tree.

SELECT P2.*
FROM Personnel AS P1, Personnel AS P2
WHERE P1.lft BETWEEN P2.lft AND P2.rgt
AND P1.emp_name = @in_employee;

2. The employee and all their subordinates. There is a nice symmetry here.

SELECT P1.*
FROM Personnel AS P1, Personnel AS P2
WHERE P1.lft BETWEEN P2.lft AND P2.rgt
AND P2.emp_name = @in_employee;

3. Add a GROUP BY and aggregate functions to these basic queries and you have hierarchical reports. For example, the total salaries which each employee controls:

SELECT P2.emp_name, SUM(S1.salary_amt)
FROM Personnel AS P1, Personnel AS P2,
Salaries AS S1
WHERE P1.lft BETWEEN P2.lft AND P2.rgt
AND P1.emp_name = S1.emp_name
GROUP BY P2.emp_name;

4. To find the level of each emp_name, so you can print the tree as an indented listing. This is what you were trying to do with recursion.

SELECT T1.emp_name,
SUM(CASE WHEN T2.lft <= T1.lft THEN 1 ELSE 0 END
+ CASE WHEN T2.rgt < T1.lft THEN -1 ELSE 0 END) AS lvl
FROM Personnel AS P1, Personnel AS P2
WHERE T2.lft <= T1.lft
GROUP BY T1.emp_name;

An untested version of this using OLAP functions might be better able to use the ordering. It needs Standard SQL and will not run in current T-SQL.

SELECT T1.emp_name,
SUM(CASE WHEN T2.lft <= T1.lft THEN 1 ELSE 0 END
+ CASE WHEN T2.rgt < T1.lft THEN -1 ELSE 0 END)
OVER (ORDER BY T1.lft
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS lvl
FROM Personnel AS P1, Personnel AS P2
WHERE T2.lft <= T1.lft;

5. To convert a nested sets model into an adjacency list model:

SELECT B.emp_name AS boss_emp_name, E.emp_name
FROM Personnel AS E
LEFT OUTER JOIN
Personnel AS B
ON B.lft
= (SELECT MAX(lft)
FROM Personnel AS S
WHERE E.lft > S.lft
AND E.lft < S.rgt);

6. To find the immediate parent of a emp_name:

SELECT MAX(P2.lft), MIN(P2.rgt)
FROM Personnel AS P1, Personnel AS P2
WHERE P1.lft BETWEEN P2.lft AND P2.rgt
AND P1.emp_name = @in_emp_name;

I have a book on TREES & HIERARCHIES IN SQL which you can get at Amazon.com right now. It has more code you can use.

--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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources