Finding where a group is applied in folder structure, query repeating results

Hi there,

I am wondering if anyone can shed any light on what I am doing wrong in this query. The end game is to be able to input a group ID and find out where this is applied to the tree structure, reporting with the full folder path. Now I have manged to do this but the output is reporting on all folders multiple times. I can't figure out why. Just now I also want this to run as a LiveReport once I figure out the correct query for Oracle SQL.

SELECT a.ParentId as "ParentID",
a.DataID as "DataID",
a.Name as "Name",
sys_connect_by_path(Name,'/') as "Path"
FROM DTree a, DtreeACL b
where a.Subtype = 0
and b.RightId = 72660
and a.DataId=b.DataID
start with a.DataID=82237
connect by prior a.DataId=a.ParentId;

Any help would be much appreciated.

Find more posts tagged with

Comments

Colin_Schmidt

I tried this and found multiple, as well. But I arbitrarily picked a folder and a RIGHTID and put those values in the DTREEACL (in SQL Developer) and two entries came up. Looking closer, the one RIGHTID I picked was in the ACL twice (Owner Group) and “Assigned”.

I tried another folder with a different RIGHTID and didn’t find any Duplicates.

SELECT a.ParentId as "ParentID", a.DataID as "DataID", a.Name as "Name",

sys_connect_by_path(Name,'/') as "Path"

FROM DtreeACL b

join DTree a on a.Subtype = 0 and a.DataId = b.DataID

where b.RightId = <####>

start with a.DataID=<###> connect by prior a.DataId=a.ParentId

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

Knowledgelink@capitalone.com

Cell: 918-904-9223

Don’t waste space with attachments – follow thesestepsto e-mail links to documents stored in Knowledgelink

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, June 02, 2016 4:57 AM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: Finding where a group is applied in folder structure, query repeating results

Finding where a group is applied in folder structure, query repeating results

Posted by Tutty, Emma On 06/02/2016 04:55 AM

Hi there,

Any help would be much appreciated.

[To post a comment, use the normal reply function]
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.

Colin_Schmidt

No, mine did the same. Skip that.

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

Finding where a group is applied in folder structure, query repeating results

Posted by Tutty, Emma On 06/02/2016 04:55 AM

Hi there,

Any help would be much appreciated.

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

Colin_Schmidt

I changed it around and the Dupes were removed. I put the DTREE 1^st and I received no Duplicates. I write my SQL this way because it clarifies where all the “WHERE”s are and why.

SELECT a.ParentId as "ParentID", a.DataID as "DataID", a.Name as "Name",

sys_connect_by_path(Name,' | ') as "Path"

FROM DTree a

join DtreeACL b on a.DataId = b.DataID and b.RightId = <####>

where a.Subtype = 0 start with a.DataID = <####> connect by prior a.DataId = a.ParentId

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, June 02, 2016 8:39 AM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: RE Finding where a group is applied in folder structure, query repeating results 2

RE Finding where a group is applied in folder structure, query repeating results 2

Posted by Schmidt, Colin On 06/02/2016 08:38 AM

No, mine did the same. Skip that.

Colin J. Schmidt

Knowledgelink Support

CapitalOne | Collaboration Technology

Finding where a group is applied in folder structure, query repeating results

Posted by Tutty, Emma On 06/02/2016 04:55 AM

Hi there,

Any help would be much appreciated.

[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:	Finding where a group is applied in folder structure, query repeating results
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

eLink User

DTREEACL.TYPE has 1 User,2 Group,3 Public access(world),4 (system not mutable)

The 0 in that field is the assigned access(below the line)

.....appu appnair appunair

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

On Thu, Jun 2, 2016 at 7:38 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

RE Finding where a group is applied in folder structure, query repeating results 2 Posted bySchmidt, ColinOn 06/02/2016 08:38 AM

No, mine did the same. Skip that. Colin J. SchmidtKnowledgelink SupportCapitalOne | Collaboration Technology

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, June 02, 2016 4:57 AM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: Finding where a group is applied in folder structure, query repeating results

Finding where a group is applied in folder structure, query repeating results

Posted by Tutty, Emma On 06/02/2016 04:55 AM

Hi there,
I am wondering if anyone can shed any light on what I am doing wrong in this query. The end game is to be able to input a group ID and find out where this is applied to the tree structure, reporting with the full folder path. Now I have manged to do this but the output is reporting on all folders multiple times. I can't figure out why. Just now I also want this to run as a LiveReport once I figure out the correct query for Oracle SQL.
SELECT a.ParentId as "ParentID",
a.DataID as "DataID",
a.Name as "Name",
sys_connect_by_path(Name,'/') as "Path"
FROM DTree a, DtreeACL b
where a.Subtype = 0
and b.RightId = 72660
and a.DataId=b.DataID
start with a.DataID=82237
connect by prior a.DataId=a.ParentId;
Any help would be much appreciated.

[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:	Finding where a group is applied in folder structure, query repeating results
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Alister Grigg

Ok, so to save you re-inventing the wheel:

https://youtu.be/gLzE3x-Oo3w

Emma_Tutty

This has worked great Colin. Many thanks for your help.