LiveReport needed to list all the sub-folder names within a folder

Hilary_Stamper

I need help putting together a query that will return all the sub-FOLDER names within a higher level folder. I don't want all the items within those subfolders to be listed, just the folder names. Does anyone have anything for this query?

Thanks!

appuGmail

select * from DTree DT where DT.ParentID=384502 ;   //all items under that parent folder  which is the folder add  /*and subtype=0*/  to restrict to folder subtypes

select DA.DataID ,DT.Name,DT.SubType from DtreeAncestors DA , DTree DT where DT.DataID=DA.DataID /*Left Join*/

and DA.AncestorID=384502;  // all items under the start folder will go even into subfolders so long as the DTreeAncestors are believable which I think should be 100 % of the time:)

The breadcrumb if needed not possible with such queries...(if in Oracle you have connect by and in SQLServer you have to write a CTE or a DB function)

..... appnair appunair( Livelink Rocks  )

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

On Wed, Nov 15, 2017 at 10:32 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

LiveReport needed to list all the sub-folder names within a folder

Posted byhilary.stamper@albertainnovates.ca (Stamper, Hilary)On 11/15/2017 11:28 AM

I need help putting together a query that will return all the sub-FOLDER names within a higher level folder. I don't want all the items within those subfolders to be listed, just the folder names. Does anyone have anything for this query?Thanks!

---

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS 16.2.2

Nick Goddard

Hi Hillary

If using SQL and you need a breadcrumb trail (as per previous post) then here is a CTE that works for me.

Just replace the Start Folder number with the top level folder dataid you are querying.

--Define CTE
WITH Folders 
AS
(
--Anchor member definition
SELECT p.[ParentID]
      ,p.[DataID]
      ,p.[Name]
      ,p.[SubType]
      ,0 AS [Level]
       , CAST(p.[Name] AS VARCHAR(255)) AS Path

FROM [DTreeCore] p
WHERE p.[DataID] = 12345 --Starting Folder
AND p.[SubType] = 0
UNION ALL

--Recursive member definition
SELECT c.[ParentID]
      ,c.[DataID]
      ,c.[Name]
      ,c.[SubType]
      ,[Level] + 1
      , CAST(Path + ' / ' + CAST(c.[Name] AS VARCHAR(255)) AS VARCHAR(255))

FROM [DTreeCore] c
INNER JOIN Folders d ON d.[DataID] = c.[ParentID]
WHERE c.[SubType] = 0
)

--Execute Query 
SELECT [ParentID],[DataID],[Name],[Level], [Path]
FROM Folders
 

Regards

Nick

appuGmail

Friendly nudge DTreeCore actually  Counts all nodes even deleted ones unless you filter by Deleted column. DTree is safer 

Ciao, Appu

On Nov 15, 2017, at 11:21 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

eLink : LiveReport needed to list all the sub-folder names within a folder

LiveReport needed to list all the sub-folder names within a folder

Posted bynicholas.goddard@derby.gov.uk (Goddard, Nicholas)On 11/15/2017 12:14 PM

Hi HillaryIf using SQL and you need a breadcrumb trail (as per previous post) then here is a CTE that works for me.Just replace the Start Folder number with the top level folder dataid you are querying.--Define CTE WITH Folders  AS ( --Anchor member definition SELECT p.[ParentID]       ,p.[DataID]       ,p.[Name]       ,p.[SubType]       ,0 AS [Level]        , CAST(p.[Name] AS VARCHAR(255)) AS PathFROM [DTreeCore] p WHERE p.[DataID] = 12345 --Starting Folder AND p.[SubType] = 0 UNION ALL--Recursive member definition SELECT c.[ParentID]       ,c.[DataID]       ,c.[Name]       ,c.[SubType]       ,[Level] + 1       , CAST(Path + ' / ' + CAST(c.[Name] AS VARCHAR(255)) AS VARCHAR(255))FROM [DTreeCore] c INNER JOIN Folders d ON d.[DataID] = c.[ParentID] WHERE c.[SubType] = 0 )--Execute Query  SELECT [ParentID],[DataID],[Name],[Level], [Path] FROM Folders  RegardsNick

---

[To post a comment, use the normal reply function]
Topic:	LiveReport needed to list all the sub-folder names within a folder
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS 16.2.2

Nizar Ghazal

I'd also recommend perhaps using DTree, if this is something you want to run with any sort of frequency, to prevent future performance issues.

DTree being a view, and DTreeCore being the actual table upon which DTree is built.

sıɹɥɔ

The reason to useDTree instead of DTreeCore, in addition to future proofing, is that LiveReports “understands” DTree.   As an example when using “DTree” you can check for certain permissions to be held by the end-user, using a %x that is set to “Filter Permissions”.   As another example to have only Folders returned in a query you can include a %x that is set to “Filter Folders”, instead of using an explicit subtype number clause. Etc.

 

 

(Obligatory mention: it’s good practice for anyone writing a query against DTree to always include relevant permissions checks, in addition to understanding the security implications of their query. This is in case someone other than a sysadmin has access to running the LiveReport.)

 

 

-chris

 

 

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Wednesday, November 15, 2017 10:21 AM
To: eLink Recipient
Subject: [EXTERNAL] - Re LiveReport needed to list all the sub-folder names within a folder

 

[EXTERNAL] - Re LiveReport needed to list all the sub-folder names within a folder

Posted bynghazal@opentext.com (Ghazal, Nizar) On 11/15/2017 01:14 PM

 

I'd also recommend perhaps using DTree, if this is something you want to run with any sort of frequency, to prevent future performance issues. DTree being a view, and DTreeCore being the actual table upon which DTree is built.

---

 

Hilary_Stamper

Thank you all,

The query that I was after seemed to work with just:

Select * from DTree DT

Where DT.ParentID=457499 and subtype=0