i have a problem
-
there is one table called emp_master in my database
it stores details of the employees, there names,age,username,password and --> "Reporting to" column
now there is one hireachy
Director
Regional Manager
Area Manager
Technical executive
each individual is reporting to his superior
empcod,other columns and Reportingto Column
now in case of technical executive he will be reporting to area
manager, and area manager will be reporting to regional manager
now what i want is get the chain of reporting to
if a regional manager is seeing the report of his region, he should be able to see the report of technical executive also
but how do i do that ??
should i write a function to get the reporting ids.
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
-
You have to write a recursive function
-
thanks for ur answer, but that was not very useful, iam using sql2000
where shoiuld i write that function in sql 2000 ?., or what exactly
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
-
in sql 2000. Wait a moment please, i get you example.
-
does anybody has an answer to this super critical problem
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
-
give me your email
-
aminnagpure@fastmail.fm post it here so that others can also see
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
-
if object_id('tmp') > 0 drop table tmp
create table tmp(id int, parentid int, code varchar(20))
insert into tmp values(1, 0, '123')
insert into tmp values(7, 6, '129')
insert into tmp values(2, 1, '124')
insert into tmp values(3, 1, '125')
insert into tmp values(4, 2, '126')
insert into tmp values(5, 2, '127')
insert into tmp values(6, 3, '128')
if object_id('MyRecursiveFunc') > 0 drop function MyRecursiveFunc
go
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'MyRecursiveFunc')
DROP FUNCTION MyRecursiveFunc
GO
CREATE FUNCTION MyRecursiveFunc
(
@id int
 
RETURNS @table_var TABLE
(
id int
, parentid int
, code varchar(20)
, lev int
 
AS
BEGIN
if @@nestlevel = 1
insert into @table_var
select *, @@nestlevel
from tmp t
where id = @id
DECLARE cur CURSOR fast_forward
FOR SELECT id, parentid, code FROM tmp where parentid = @id
DECLARE @tid int, @parentid int, @code varchar(20)
OPEN cur
FETCH NEXT FROM cur INTO @tid, @parentid, @code
WHILE @@fetch_status = 0
BEGIN
insert into @table_var values(@tid, @parentid, @code, @@nestlevel)
insert into @table_var select * from MyRecursiveFunc(@tid)
FETCH NEXT FROM cur INTO @tid, @parentid, @code
END
CLOSE cur
DEALLOCATE cur
RETURN
END
GO
SELECT * FROM dbo.MyRecursiveFunc(3)
GO
if object_id('tmp') > 0 drop table tmp
if object_id('MyRecursiveFunc') > 0 drop function MyRecursiveFunc
-
thanks for answer, but is there any other simple way like correlated subquery, iam new to 2000 just shifted from 7 to 2000
never written any function
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
-
There is no any query. But there is many ways. One of each is build tree in the table with triggers. I never explore it, but i think it possible.
If your nestlevel is not big you can use two or three tables.
Also, there is other method to hadle tree structure(not with id and parentid fileds). But I think with that fileds is more powerfull with Yukon. In Yukon we can use of simple query(recursuve query).
-
Sukio,
There is an example query in "Books on Line". To find it, lookup "expanding hierarchies" in the BOL index. Be advised that it is not "bug free" and will require a bit of tweeking.
--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)
Intro to Tally Tables and Functions -
Look at Nigel's work on hierarchies and see if that solves your problem.
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply