Re: SQL question on hierarchical data

eric__hogue_at_my-dejanews.com wrote:
>
> In article <HwAS1.2910$yo1.55067256_at_news.ipass.net>,
> "David Sisk" <davesisk_at_ipass.net> wrote:
> > I think you're going to have to switch back to ORACLE comrade! :)
> >
> I don't think this will be necessary.
>
> > Sunil Godithi wrote in message ...
> > >hi,
> > >I have recently switched to SQL Server from oracle world and I am having
> > >problem with Select statement.
> > >
> > >I have a table called emp with structure like:
> > >EMPNO ENAME MGR
> > >
> > >MGR is the EMPNO of the manager. I need to get a list of employees like
 this
> > >(select statement is Oracle specific, I need something similar for
> > >SQLServer)...
> > >SQL> l
> > > 1 select level
> > > 2 , ename
> > > 3 , empno
> > > 4 , mgr
> > > 5 from emp
> > > 6 connect by prior empno = mgr
> > > 7* start with job = 'PRESIDENT'
> > >SQL> /
> > >
> > > LEVEL ENAME EMPNO MGR
> > > --------- ---------- ------- ---------
> > > 1 KING 7839
> > > 2 JONES 7566 7839
> > > 3 SCOTT 7788 7566
> > > 4 ADAMS 7876 7788
> > > 3 FORD 7902 7566
> > > 4 SMITHXX 7369 7902
> > > 2 BLAKE 7698 7839
> > > 3 ALLEN 7499 7698
> > > 3 WARD 7521 7698
> > > 3 MARTIN 7654 7698
> > > 3 TURNER 7844 7698
> > > 3 JAMES 7900 7698
> > > 2 CLARK 7782 7839
> > > 3 MILLER 7934 7782
> > >
> > >14 rows selected.
> > >
> > >Any help is appreciated..
> > >
> You can mention the table twice in a select statment, using different
> abbreviations (at least, in 11.5 on NT)
>
> select e.level,
> e.ename,
> e.empno,
> m.empno as MGR
> from emp e,
> emp m
> where e.mgr=m.empno
>
> > >Sunil
> > >
> Eric
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

Unfortunately, I think you missed the complication of the "level" column.

This is a classic case of the "Bill Of Materials" problem. Let me try and remember how to do it .... (By the way, Oracle's "connect by prior" is non-standard .... but works really well).

In Sybase it is not possible to do it in one statement.

If I recall, one way to do it is as follows:

select @level = 1
select @breadth = 1 -- the number of people at the current level

create table #level
as
(mgr data-type)

create table #result
as

(level   integer,
name     varchar,
id       integer,
mgr      integer null)

insert into #level values (null) -- or whatever value you have in the mgr column of people without managers.

while @breadth > 0 begin
  insert into #result
  select @level, ENAME, EMPNO, MGR
  from emp, #level
  where MGR = mgr

  delete #level

  insert into #level
  select id
  from #result
  where level = @level

  select @breadth = @@rowcount,

         @level = @level + 1

end

select * from #result

as a brief explanation, in #level we keep a list of managers at a given level. This starts with level 0 (null), which has one entry. We then populate #result with all emp's which have a manager in the #level list.

Once we have the level 1 in #result, we repopulate #level with the people who are level 1, and find all the people who report to them, call them level 2, and put them in #result. We then re-populate #level, and keep on going.

@breadth keeps track of how many people are at a given level. When this is 0, we have reached the end of the organisation.

One benifit of this is that you can start with any person, or list of people depending on who you put in the initial #level. Also, you can also limit the number of levels of information to report to.

Anyway, that is just one way of doing it. This will work for all versions of Sybase I know, although there may be better ways of doing it. I imagine that there may be a "nice" way in one of the later/latest releases.

This is something I have just written now, and haven't tested it at all. I am just trying to describe a method, although I am pretty sure that the above code should work quite well. The performance issues will creep in if there are many people at any level. This shouldn't be a real problem unless you have more than a couple of thousand records or so. Try it, see what happens.

Good Luck

gus Received on Wed Oct 07 1998 - 00:00:00 CDT