Recursive SQL to ignore a specific leg of fileplan

Hi Forum

Wondering if anyone here knows how i might do the following ?

I am trying to find all the areas of our filepan where a specific group is applied which i can do via the following Oracle SQL

Select d1.DataID, d1.Name "Name", k1.Name "Group Name"
From DTree d1, DTreeACL d2, KUAF k1
Where d2.RightID IN (Select id From KUAF
Where Name LIKE %1
And Type=1)
And d1.DataID = d2.DataID
And d2.RightID = k1.ID
And d1.OwnerID = -2000

Now the only thing i would like to do is igonre one leg of our fileplan from a specific folder down , our fileplan is hierarchial and there is one leg of it i would like to ignore a root folder and all children.

Wondering if anyone had an idea how to perform this ?

Regards

Mike.

Find more posts tagged with

Comments

Lindsay_Davies

Hi Mike,

You could either write a hierarchical query (in Oracle using ‘connect by prior’ or in SQL server a recursive common table expression).

If you do this and the unwanted leg is at the top level, then you could start with all the direct children of the Enterprise that you do want.

Or you could extend what you have so far and exclude items that have the DataID of the unwanted leg as an ancestor.

I think something like this will work for you..

Select d1.DataID, d1.Name "Name", k1.Name "Group Name"
From DTree d1, DTreeACL d2, KUAF k
Where d2.RightID IN (Select id From KUAF
Where Name LIKE %1
And Type=1)
And d1.DataID = d2.DataID
And d2.RightID = k1.ID
And d1.OwnerID = -2000
AND not exist (select 1 from DTreeAncestors where DataID = d1.DataId and AncestorID = <DataID of unwanted leg>)

This would rely on your Ancestry tables being accurate.

Good luck

Lindsay

EU-Escalations Team

OpenText UK

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Friday, June 19, 2015 2:37 PM
To: eLink Recipient
Subject: Recursive SQL to ignore a specific leg of fileplan

Recursive SQL to ignore a specific leg of fileplan

Posted bymichael.fennell@fca.org.uk (Fennell, Michael) On 06/19/2015 09:34 AM

Hi Forum

Wondering if anyone here knows how i might do the following ?

I am trying to find all the areas of our filepan where a specific group is applied which i can do via the following Oracle SQL

Wondering if anyone had an idea how to perform this ?

Regards

Mike.

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

Colin_Schmidt

I'd try to incorporate the DTREEANCESTORS table. I'd restructure the query to start with the KUAF table to retrieve the specific Group, then the DTREEACL table and then the DTREE table. I'd use then possibly a subquery on the line with the DTREE. I might have to use the DTREEANCESTOR table twice. But that's a guess.

It looks something like this...untested:

Select dt.subtype, dt.DataID, dt.Name "Name", k.Name "Group Name"

From KUAF k

join DTreeACL dta on dta.rightid = k.id <--The ACL record(s) with that Group

join DTree dt on dta.dataid = dt.dataid and dt.ownerid = -2000 <--ENTERPRISE WS, but excluding projects

and NOT EXISTS (select dtan.dataid from dtreeancestors dtan where dtan.ancestorid = <dataid>) <--and the object must not exist under this AncestorID

Where k.name like %1 and k.type = 1 <--The specific Group

Colin J.

On Fri, Jun 19, 2015 at 9:36 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Recursive SQL to ignore a specific leg of fileplan Posted bymichael.fennell@fca.org.uk (Fennell, Michael)On 06/19/2015 09:34 AM

Hi ForumWondering if anyone here knows how i might do the following ?I am trying to find all the areas of our filepan where a specific group is applied which i can do via the following Oracle SQLSelect d1.DataID, d1.Name "Name", k1.Name "Group Name"
>From DTree d1, DTreeACL d2, KUAF k1
Where d2.RightID IN (Select id From KUAF
Where Name LIKE %1
And Type=1)
And d1.DataID = d2.DataID
And d2.RightID = k1.ID
And d1.OwnerID = -2000Now the only thing i would like to do is igonre one leg of our fileplan from a specific folder down , our fileplan is hierarchial and there is one leg of it i would like to ignore a root folder and all children.Wondering if anyone had an idea how to perform this ?RegardsMike.
[To post a comment, use the normal reply function]
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

This electronic message contains information from MeadWestvaco Corporation (mwv.com) or subsidiary companies, which may be confidential, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipient(s) named. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify MeadWestvaco immediately at postmaster@mwv.com.