Re: SQL question on hierarchical data

In comp.databases.sybase gus <gus_goose_at_hotmail.com> wrote:
: eric__hogue_at_my-dejanews.com wrote:

:> 
:> In article <HwAS1.2910$yo1.55067256_at_news.ipass.net>,
:>   "David Sisk" <davesisk_at_ipass.net> wrote:
:> > 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

[snip]

: 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

[snip]

Ack! Evil iterative processing! :) Yes...there is another way.

Check out:

        http://reality.sgi.com/pablo/Sybase_FAQ/Q7.7.html

this is a write-up I did (although the algo isn't mine) a year or two ago on a relatively elegant (and extremely efficient) way in which transitive closure statements can be achieved. I have successfully used this algorithm in a very complex environment with arbitrary depth, staggered hierarchies. The major drawback of the algo, however, is that it is very processing intensive on altering the structure of the hierarchy (i.e. if your hierarchical structure changes by the second, then it is probably not the solution for you).

-scott

-- 
Scott C. Gray                 gray_at_voicenet.com     "my keybard is brken"
Sybase Professional Services  scott.gray_at_sybase.com
   http://www.voicenet.com/~gray/sqsh.html