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

    &nbsp

    RETURNS @table_var TABLE

     (

      id int

      , parentid int

      , code varchar(20)

      , lev int

    &nbsp

    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)

  • Look at Nigel's work on hierarchies and see if that solves your problem.

    http://www.nigelrivett.net/

    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