List level 1 folders

Baber_Amin1

Hi,

I am using below query to list only Level 1 folders. Below query list everything underneath. How to apply level filter?

 

SELECT dt.name

from DTreeAncestors anc, DTree dt

where anc.AncestorID = XXXXX and

anc.DataID = dt.DataID

and dt.subtype in (0)

ORDER BY dt.name

appuGmail

If Oracle try "connect by prior" searches in the LR forum inbuilt given by Oracle. The level is also a pseudo column in Oracle

I am not posting that because literally, you should find 100's of postings on that.

If in SQLserver one needs to walk the hierarchy for the e.g search for CTE's in SQLserver forums in that regard.OT also has lots of

customers in SQL so this is doable as well.

I think I got the idea for this from KB someone posted this I use this in my SQLserver work no idea how to repurpose for DTreeAncestors

perhaps you don't need to walk the hierarchy as OT already has the parent-child in that but not sure how you would level it.

---------------------------A working query to print some permission info on Folders---------------------------

 WITH SubTree ( Name, DataID, ParentID, level, SubType, pathstr)

AS  (Select DT.Name, DT.DataID, DT.ParentID, 0, DT.SubType, CAST (Name as NVARCHAR(MAX))

       FROM DTree DT WHERE DT.DataID = 8766002 -- do not do EWS(2000) if content is large it may run for a while

   UNION ALL

   SELECT p.Name, p.DataID, p.ParentID, level + 1, p.SubType,

          pathstr + ':' + cast(p.Name AS nvarchar(max))

   FROM DTree p

 INNER JOIN SubTree s ON s.DataID = p.ParentID

WHERE p.SubType = 0 

  )

--use that CTE output and try to print some permission things  

  Select REPLICATE('..\',level) + ST.Name as Name, ST.DataID, ST.level, ST.SubType, ST.pathstr as "Full Path",

ODT.ACLType, (select Name from KUAF where  ID=ODT.RightID)"User/Group" ,ODT.[Permissions]

FROM SubTree ST , DTreeACL ODT where ST.DataID=ODT.DataID and ODT.ACLType <> 4   ORDER BY ST.pathstr, ST.DataID,ODT.ACLType 

--if you use Level=0 1 or somehing like that you can actually limit as well.

--Replicate is a sqlserver function to show some indentation

******************************************************************

Ciao,

..... appnair appunair( Livelink Rocks  )

Well, if I called the wrong number, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937 

On Mon, Jan 7, 2019 at 4:42 PM eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

List level 1 folders

Posted bybaber.amin@albertainnovates.ca (Amin, Baber)On 01/07/2019 05:39 PM

Hi,I am using below query to list only Level 1 folders. Below query list everything underneath. How to apply level filter? SELECT dt.namefrom DTreeAncestors anc, DTree dt where anc.AncestorID = XXXXX and anc.DataID = dt.DataIDand dt.subtype in (0)ORDER BY dt.name

---

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	My Support