Best Oracle SQL statement in Livelink

Ever wish you could start with a folder in dtree and find all of the children of that folder, and then all of the children of that folder, and then all of the children of that folder, etc...I was out looking in the Oracle manual trying to figure out how to do a particular select when I ran across something called Hierarchical Queries. That kind of peaked my interest so I checked it out.select dataid, namefrom dtreestart with dataid = 12104040connect by prior dataid = parentidReplace the dataid with anyone of your choosing and try running this query. It should give you the dataid and name that you requested and then anything below that dataid.I don't know if the other DBMS support this query but it might be worth finding out.Cheers!

Find more posts tagged with

Comments

Alex_Kowalenko_(AKowalenko_(Delete)_355734)

I agree that this "hierarchical tree walk query" is very useful. It is supported by the other RDBMS.

John_Buckley_(jpbuckley_(Delete)_1483522)

The "connect by" function in Oracle is great little secret weapon for unlocking the KUAF & DTreeACL Tables, but I have never found it's equivalent as a function in SQL Server 6.5. Does anyone know if it might be found in SQL Server 7.0?

Primavera_General_Account_(primav021_-_(deleted))

As far as I know, there is no Connect By in SQL 7.0. This might be acomplished in SQL 7 by writing a simple query tho'.

Colin_Schmidt

This puts the path in a single line with a separator from EWS down to the file name.select dataid, LEVEL, 'Enterprise' || SYS_CONNECT_BY_PATH (name, ' | ') " EWS Path" from dtreeCONNECT BY PRIOR dataid=parentid START WITH (name='' AND parentid=2000)

Mary_Slane_Corona

Works great. Thank you.Mary Corona