Re: Materialised Path Heirarchy

I see. Query should use the delimiter:

select e1.ename from emp e1, emp e2
where e2.path like e1.path || '.%'
and e2.name = 'FORD'

--
David Portas
------------
Please reply only to the newsgroup
--

"David Portas" <REMOVE_BEFORE_REPLYING_dportas_at_acm.org> wrote in message
news:#tnI$fF9CHA.2216_at_TK2MSFTNGP12.phx.gbl...


> 1. Yes


>

> 2. I don't see the problem. In the example query you give there is no  >

or


> < predicate anyway.


>

> To take another example from the article:

>

> select e1.ename from emp e1, emp e2

> where e2.path > e1.path and e2.path < e1.path || 'Z'

> and e2.name = 'FORD'

>

> Alphanumeric sorting isn't a problem here because you are simply comparing

> relative paths. The problem you describe is relevant only to *sorting* on

> numeric strings.

>

> --

> David Portas

> ------------

> Please reply only to the newsgroup

> --

>

> "stu" <smcgouga_at_nospam.co.uk> wrote in message

> news:b5umbg$bhm$1$8300dec7_at_news.demon.co.uk...

> > http://www.dbazine.com/tropashko4.html

> > (about 2 pages down they talk about the materialised path)

> >

> > I need to set up a hierarchy in a database.  I would like to understand

> the

> > nested set but can not quite get my head round it as quickly as is

> required.

> > So that aside I would like to ask some questions about the materialised

> > path.

> >

> > 1.  Am i correct in saying an index over the path field could be used if

I


> > parse the path on the client and return a query with an SQL IN


statement?


> > eg select * from .... where path in ('1' 1.1', '1.2', ...)


> >

> > 2.  Do queries such as

> > select e1.ename from emp e1, emp e2

> > where e2.path like e1.path || '%'

> > and e2.name = 'FORD'

> > only work for up to 9 nodes in one level?  remembering '8' < '9' > '10'

> when

> > comparing strings.

> >

> > The only way I can see round this is to always use x characters to

> represent

> > each level.  e.g '0008' < '0009' < '0010'

> >

> > But this would require hard coding the max number of nodes in each level

> > (9999 in this case) which i do not want to do.

> >

> > comments or pointers?

> >

> > cheers

> > stu

> >

> >

>

>