nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Indented Folder Structure

Colin_Schmidt

In this query, I can get an indented folder structure.......IF I run it in SQL Developer. In LR: no.
Does anyone know of something that will? If I have to run this for Owners, that means they can't and it ties up some of my time. I tried it under a WebReport and get the same.

select lpad('..', 2*level-1)||dt.name "Folder",
NVL((select to_char(sum(dv.datasize), '999,999,999,999,999') from dtreecore dta join dversdata dv on dta.dataid = dv.docid and dta.versionnum = dv.version where dt.dataid = dta.parentid), 0) "Bytes",
(select count(*) from dtreecore dtc where dtc.parentid = dt.dataid) "Docs",
sys_connect_by_path(dt.name, '\') "Path"
from dtreecore dt
where dt.subtype = 0 and dt.dataid in (select dtf.dataid from dtreeancestors dtan join dtreecore dtf on dtan.dataid = dtf.dataid and dtf.subtype = 0 where dtan.ancestorid = %2)
start with dt.dataid = %1 connect by dt.parentid = prior dt.dataid
Order by "Path"

Find more posts tagged with

Comments

Lindsay_Davies

Hi Colin,

I don’t know why it would work in SQL Developer and not in a Livereport.

I am assuming you are using autolivereport as the output mode.

There are a few parts to your query that I would change, for the reasons laid out below.

First – why are you using DTreeCore?

That means you would be reporting logically deleted items as well as the items that users would find in the Enterprise or other Volume.

You have four places where you define the table DTreeCore too – so it would be likely run slowly and to impact the running system.

You could remove some code as it does nothing useful - that is the DTreeAncestors piece which also links to a copy of DTreeCore.

Also you can pick up the count of items from DTree.Childcount instead of using a sub-query against DTree.

As you are using an Oracle database, Connect By Prior is how you walk the hierarchy in a structured manner.

DTreeAncestors does a similar thing but returns an unstructured list of child dataids.

You don’t need to do that AS WELL as use connect by prior.

You have not explained why you are using two input parameters (%1 and %2).

I am assuming that they both have the same value, otherwise it makes no sense to me.

The connect by prior will return all the children of %1 in hierarchical ordered list, and the ancestors query will return all the children of %2 in an unstructured list

So using where %1’s dataid is in the list of %2’s dataids will be a certainty.

If %2 was a child of %1 you would just start lower down and use that value for %1.

If %2 is an ancestor of %1 it is equally irrelevant.

If %2’s list has nothing to do with %1’s, you will get nothing back.

Finally, as Best Practice, I personally would not use a ‘\’ as the separator for sys_connect_by_path

as that can be valid character in an object name in Content Server and would therefore mis-represent a folder path.

I would use a ‘:’, which is what Content Server uses when listing paths internally (e.g. in the audit tables and move / copy dialogs).

I have formatted your original query to make it more readable.

The coloured bits will hopefully remain when this appears back in the discussion and in emails out to readers.

Green – I will amend, Red – I will remove.

select

lpad('..', 2*level-1)||dt.name "Folder"

,NVL(

(select to_char(sum(dv.datasize), '999,999,999,999,999')

from dtreecore dta join dversdata dv

on dta.dataid = dv.docid and dta.versionnum = dv.version

where dt.dataid = dta.parentid)

, 0) "Bytes"

,(select count(*) from dtreecore dtc where dtc.parentid = dt.dataid) "Docs"

,sys_connect_by_path(dt.name, '\') "Path"

from dtreecore dt

where dt.subtype = 0

and dt.dataid in

(select dtf.dataid

from dtreeancestors dtan join dtreecore dtf

on dtan.dataid = dtf.dataid

and dtf.subtype = 0

where dtan.ancestorid = %2)

start with dt.dataid = %1

connect by dt.parentid = prior dt.dataid

Order by "Path"

Here is my updated version.

select

lpad('..', 2*level-1)||dt.name "Folder"

,NVL(

(select to_char(sum(dv.datasize), '999,999,999,999,999')

fromdtree dta join dversdata dv

on dta.dataid = dv.docid and dta.versionnum = dv.version

where dt.dataid = dta.parentid)

, 0) "Bytes"

,dt.childcount "Docs"

,sys_connect_by_path(dt.name, '\') "Path"

from dtree dt

where dt.subtype = 0

start with dt.dataid = %1

connect by dt.parentid = prior dt.dataid

Order by "Path"

Hope this suggestion works for you.

Regards

Lindsay

Lindsay Davies
Information Systems Administrator
RES
D +44 (0)1923 299 378

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 08 June 2016 21:16
To: eLink Recipient
Subject: Indented Folder Structure

Indented Folder Structure

Posted by Schmidt, Colin On 06/08/2016 04:14 PM

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

CONFIDENTIALITY NOTICE: This e-mail, including any attachments, contains information that may be confidential, and is protected by copyright. It is directed to the intended recipient(s) only. If you have received this e-mail in error please e-mail the sender by replying to this message, and then delete the e-mail. Unauthorised disclosure, publication, copying or use of this e-mail is prohibited. Any communication of a personal nature in this e-mail is not made by or on behalf of any RES group company. E-mails sent or received may be monitored to ensure compliance with the law, regulation and/or our policies.

Colin_Schmidt

I thought since DTREE is now a “View”, DTREECORE ought to be the table to use.

I use the DTREEANCESTORS to get all Folders under a particular parent.

The two parameters are the same Value. LR wants you to use “%1”, for instance, once. If you need to reference it more than once, you have to use %2, but refer it back to %1.

Regardless of “best practice” for a separator, the actual character used depends upon the characters used in the Folder names. I’ve used “|”, “:”, “~”, and now “/”, but when another high parent folder is used, “/” fails with the same Oracle error.

Thank you and I will try it.

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Sunday, June 12, 2016 10:35 AM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: RE Indented Folder Structure

RE Indented Folder Structure

Posted by Davies, Lindsay On 06/12/2016 10:34 AM

Hi Colin,

I don’t know why it would work in SQL Developer and not in a Livereport.

I am assuming you are using autolivereport as the output mode.

There are a few parts to your query that I would change, for the reasons laid out below.

First – why are you using DTreeCore?

That means you would be reporting logically deleted items as well as the items that users would find in the Enterprise or other Volume.

You have four places where you define the table DTreeCore too – so it would be likely run slowly and to impact the running system.

You could remove some code as it does nothing useful - that is the DTreeAncestors piece which also links to a copy of DTreeCore.

Also you can pick up the count of items from DTree.Childcount instead of using a sub-query against DTree.

As you are using an Oracle database, Connect By Prior is how you walk the hierarchy in a structured manner.

DTreeAncestors does a similar thing but returns an unstructured list of child dataids.

You don’t need to do that AS WELL as use connect by prior.

You have not explained why you are using two input parameters (%1 and %2).

I am assuming that they both have the same value, otherwise it makes no sense to me.

The connect by prior will return all the children of %1 in hierarchical ordered list, and the ancestors query will return all the children of %2 in an unstructured list

So using where %1’s dataid is in the list of %2’s dataids will be a certainty.

If %2 was a child of %1 you would just start lower down and use that value for %1.

If %2 is an ancestor of %1 it is equally irrelevant.

If %2’s list has nothing to do with %1’s, you will get nothing back.

Finally, as Best Practice, I personally would not use a ‘\’ as the separator for sys_connect_by_path

as that can be valid character in an object name in Content Server and would therefore mis-represent a folder path.

I would use a ‘:’, which is what Content Server uses when listing paths internally (e.g. in the audit tables and move / copy dialogs).

I have formatted your original query to make it more readable.

The coloured bits will hopefully remain when this appears back in the discussion and in emails out to readers.

Green – I will amend, Red – I will remove.

select

lpad('..', 2*level-1)||dt.name "Folder"

,NVL(

(select to_char(sum(dv.datasize), '999,999,999,999,999')

from dtreecore dta join dversdata dv

on dta.dataid = dv.docid and dta.versionnum = dv.version

where dt.dataid = dta.parentid)

, 0) "Bytes"

,(select count(*) from dtreecore dtc where dtc.parentid = dt.dataid) "Docs"

,sys_connect_by_path(dt.name, '\') "Path"

from dtreecore dt

where dt.subtype = 0

and dt.dataid in

(select dtf.dataid

from dtreeancestors dtan join dtreecore dtf

on dtan.dataid = dtf.dataid

and dtf.subtype = 0

where dtan.ancestorid = %2)

start with dt.dataid = %1

connect by dt.parentid = prior dt.dataid

Order by "Path"

Here is my updated version.

select

lpad('..', 2*level-1)||dt.name "Folder"

,NVL(

(select to_char(sum(dv.datasize), '999,999,999,999,999')

fromdtree dta join dversdata dv

on dta.dataid = dv.docid and dta.versionnum = dv.version

where dt.dataid = dta.parentid)

, 0) "Bytes"

,dt.childcount "Docs"

,sys_connect_by_path(dt.name, '\') "Path"

from dtree dt

where dt.subtype = 0

start with dt.dataid = %1

connect by dt.parentid = prior dt.dataid

Order by "Path"

Hope this suggestion works for you.

Regards

Lindsay

Lindsay Davies
Information Systems Administrator
RES
D +44 (0)1923 299 378

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 08 June 2016 21:16
To: eLink Recipient
Subject: Indented Folder Structure

Indented Folder Structure

Posted by Schmidt, Colin On 06/08/2016 04:14 PM

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

[To post a comment, use the normal reply function]
Topic:	Indented Folder Structure
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

The information contained in this e-mail is confidential and/or proprietary to Capital One and/or its affiliates and may only be used solely in performance of work or services for Capital One. The information transmitted herewith is intended only for use by the individual or entity to which it is addressed. If the reader of this message is not the intended recipient, you are hereby notified that any review, retransmission, dissemination, distribution, copying or other use of, or taking of any action in reliance upon this information is strictly prohibited. If you have received this communication in error, please contact the sender and delete the material from your computer.

Lindsay_Davies

Hi Colin,

The view DTree is OK to use in your queries.

As there are no calculated columns in the view ‘DTree’, you can safely run update statements against the data in the columns in that view, if you need to.

In Oracle, using a connect by prior is easier to code than writing a the necessary code to join DTree to DTreeAncestors.

Yup, I had assumed your reasoning for two parameters was that you cannot use one twice.

With regard to the separator for sys_connect_by_prior - the colon character ‘:’ is not allowed in an object name in Content Server, therefore it is always a reliable choice. It is not allowed in Windows or Unix file systems which is why Content Server uses it for its own separator purposes.

Regards

Lindsay

Lindsay Davies
Information Systems Administrator
RES
D +44 (0)1923 299 378

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 13 June 2016 15:19
To: eLink Recipient
Subject: RE RE Indented Folder Structure

RE RE Indented Folder Structure

Posted by Schmidt, Colin On 06/13/2016 10:18 AM

I thought since DTREE is now a “View”, DTREECORE ought to be the table to use.

I use the DTREEANCESTORS to get all Folders under a particular parent.

The two parameters are the same Value. LR wants you to use “%1”, for instance, once. If you need to reference it more than once, you have to use %2, but refer it back to %1.

Thank you and I will try it.

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

RE Indented Folder Structure

Posted by Davies, Lindsay On 06/12/2016 10:34 AM

Hi Colin,

I don’t know why it would work in SQL Developer and not in a Livereport.

I am assuming you are using autolivereport as the output mode.

There are a few parts to your query that I would change, for the reasons laid out below.

First – why are you using DTreeCore?

That means you would be reporting logically deleted items as well as the items that users would find in the Enterprise or other Volume.

You have four places where you define the table DTreeCore too – so it would be likely run slowly and to impact the running system.

You could remove some code as it does nothing useful - that is the DTreeAncestors piece which also links to a copy of DTreeCore.

Also you can pick up the count of items from DTree.Childcount instead of using a sub-query against DTree.

As you are using an Oracle database, Connect By Prior is how you walk the hierarchy in a structured manner.

DTreeAncestors does a similar thing but returns an unstructured list of child dataids.

You don’t need to do that AS WELL as use connect by prior.

You have not explained why you are using two input parameters (%1 and %2).

I am assuming that they both have the same value, otherwise it makes no sense to me.

The connect by prior will return all the children of %1 in hierarchical ordered list, and the ancestors query will return all the children of %2 in an unstructured list

So using where %1’s dataid is in the list of %2’s dataids will be a certainty.

If %2 was a child of %1 you would just start lower down and use that value for %1.

If %2 is an ancestor of %1 it is equally irrelevant.

If %2’s list has nothing to do with %1’s, you will get nothing back.

Finally, as Best Practice, I personally would not use a ‘\’ as the separator for sys_connect_by_path

as that can be valid character in an object name in Content Server and would therefore mis-represent a folder path.

I would use a ‘:’, which is what Content Server uses when listing paths internally (e.g. in the audit tables and move / copy dialogs).

I have formatted your original query to make it more readable.

The coloured bits will hopefully remain when this appears back in the discussion and in emails out to readers.

Green – I will amend, Red – I will remove.

select

lpad('..', 2*level-1)||dt.name "Folder"

,NVL(

(select to_char(sum(dv.datasize), '999,999,999,999,999')

from dtreecore dta join dversdata dv

on dta.dataid = dv.docid and dta.versionnum = dv.version

where dt.dataid = dta.parentid)

, 0) "Bytes"

,(select count(*) from dtreecore dtc where dtc.parentid = dt.dataid) "Docs"

,sys_connect_by_path(dt.name, '\') "Path"

from dtreecore dt

where dt.subtype = 0

and dt.dataid in

(select dtf.dataid

from dtreeancestors dtan join dtreecore dtf

on dtan.dataid = dtf.dataid

and dtf.subtype = 0

where dtan.ancestorid = %2)

start with dt.dataid = %1

connect by dt.parentid = prior dt.dataid

Order by "Path"

Here is my updated version.

select

lpad('..', 2*level-1)||dt.name "Folder"

,NVL(

(select to_char(sum(dv.datasize), '999,999,999,999,999')

fromdtree dta join dversdata dv

on dta.dataid = dv.docid and dta.versionnum = dv.version

where dt.dataid = dta.parentid)

, 0) "Bytes"

,dt.childcount "Docs"

,sys_connect_by_path(dt.name, '\') "Path"

from dtree dt

where dt.subtype = 0

start with dt.dataid = %1

connect by dt.parentid = prior dt.dataid

Order by "Path"

Hope this suggestion works for you.

Regards

Lindsay

Lindsay Davies
Information Systems Administrator
RES
D +44 (0)1923 299 378

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 08 June 2016 21:16
To: eLink Recipient
Subject: Indented Folder Structure

Indented Folder Structure

Posted by Schmidt, Colin On 06/08/2016 04:14 PM

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

[To post a comment, use the normal reply function]
Topic:	Indented Folder Structure
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

[To post a comment, use the normal reply function]
Topic:	Indented Folder Structure
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Colin_Schmidt

I tried this indented query in IE and Chrome to see if the Browser made a difference, but they didn’t. Both produced “trimmed” leading spaces on the Column. I stuck a leading character:

select ‘>’||lpad('..', 2*level-1)||dt.name "Folder"…..,

But it made no difference. The 3^rd parameter(that I just found) “fixes” it, so to speak.

If I do this:

select lpad('..', 2*level-1, ‘.’)||dt.name "Folder"…..,

It places a “.” for every indented space and this may work for our Users.

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, June 13, 2016 11:26 AM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: RE RE RE Indented Folder Structure

RE RE RE Indented Folder Structure

Posted by Davies, Lindsay On 06/13/2016 11:25 AM

Hi Colin,

The view DTree is OK to use in your queries.

As there are no calculated columns in the view ‘DTree’, you can safely run update statements against the data in the columns in that view, if you need to.

In Oracle, using a connect by prior is easier to code than writing a the necessary code to join DTree to DTreeAncestors.

Yup, I had assumed your reasoning for two parameters was that you cannot use one twice.

Regards

Lindsay

Lindsay Davies
Information Systems Administrator
RES
D +44 (0)1923 299 378

RE RE Indented Folder Structure

Posted by Schmidt, Colin On 06/13/2016 10:18 AM

I thought since DTREE is now a “View”, DTREECORE ought to be the table to use.

I use the DTREEANCESTORS to get all Folders under a particular parent.

The two parameters are the same Value. LR wants you to use “%1”, for instance, once. If you need to reference it more than once, you have to use %2, but refer it back to %1.

Thank you and I will try it.

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

RE Indented Folder Structure

Posted by Davies, Lindsay On 06/12/2016 10:34 AM

Hi Colin,

I don’t know why it would work in SQL Developer and not in a Livereport.

I am assuming you are using autolivereport as the output mode.

There are a few parts to your query that I would change, for the reasons laid out below.

First – why are you using DTreeCore?

That means you would be reporting logically deleted items as well as the items that users would find in the Enterprise or other Volume.

You have four places where you define the table DTreeCore too – so it would be likely run slowly and to impact the running system.

You could remove some code as it does nothing useful - that is the DTreeAncestors piece which also links to a copy of DTreeCore.

Also you can pick up the count of items from DTree.Childcount instead of using a sub-query against DTree.

As you are using an Oracle database, Connect By Prior is how you walk the hierarchy in a structured manner.

DTreeAncestors does a similar thing but returns an unstructured list of child dataids.

You don’t need to do that AS WELL as use connect by prior.

You have not explained why you are using two input parameters (%1 and %2).

I am assuming that they both have the same value, otherwise it makes no sense to me.

The connect by prior will return all the children of %1 in hierarchical ordered list, and the ancestors query will return all the children of %2 in an unstructured list

So using where %1’s dataid is in the list of %2’s dataids will be a certainty.

If %2 was a child of %1 you would just start lower down and use that value for %1.

If %2 is an ancestor of %1 it is equally irrelevant.

If %2’s list has nothing to do with %1’s, you will get nothing back.

Finally, as Best Practice, I personally would not use a ‘\’ as the separator for sys_connect_by_path

as that can be valid character in an object name in Content Server and would therefore mis-represent a folder path.

I would use a ‘:’, which is what Content Server uses when listing paths internally (e.g. in the audit tables and move / copy dialogs).

I have formatted your original query to make it more readable.

The coloured bits will hopefully remain when this appears back in the discussion and in emails out to readers.

Green – I will amend, Red – I will remove.

select

lpad('..', 2*level-1)||dt.name "Folder"

,NVL(

(select to_char(sum(dv.datasize), '999,999,999,999,999')

from dtreecore dta join dversdata dv

on dta.dataid = dv.docid and dta.versionnum = dv.version

where dt.dataid = dta.parentid)

, 0) "Bytes"

,(select count(*) from dtreecore dtc where dtc.parentid = dt.dataid) "Docs"

,sys_connect_by_path(dt.name, '\') "Path"

from dtreecore dt

where dt.subtype = 0

and dt.dataid in

(select dtf.dataid

from dtreeancestors dtan join dtreecore dtf

on dtan.dataid = dtf.dataid

and dtf.subtype = 0

where dtan.ancestorid = %2)

start with dt.dataid = %1

connect by dt.parentid = prior dt.dataid

Order by "Path"

Here is my updated version.

select

lpad('..', 2*level-1)||dt.name "Folder"

,NVL(

(select to_char(sum(dv.datasize), '999,999,999,999,999')

fromdtree dta join dversdata dv

on dta.dataid = dv.docid and dta.versionnum = dv.version

where dt.dataid = dta.parentid)

, 0) "Bytes"

,dt.childcount "Docs"

,sys_connect_by_path(dt.name, '\') "Path"

from dtree dt

where dt.subtype = 0

start with dt.dataid = %1

connect by dt.parentid = prior dt.dataid

Order by "Path"

Hope this suggestion works for you.

Regards

Lindsay

Lindsay Davies
Information Systems Administrator
RES
D +44 (0)1923 299 378

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 08 June 2016 21:16
To: eLink Recipient
Subject: Indented Folder Structure

Indented Folder Structure

Posted by Schmidt, Colin On 06/08/2016 04:14 PM

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

[To post a comment, use the normal reply function]
Topic:	Indented Folder Structure
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

[To post a comment, use the normal reply function]
Topic:	Indented Folder Structure
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

[To post a comment, use the normal reply function]
Topic:	Indented Folder Structure
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center