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; } } }

LiveReport of all folders without a certain group applied

Imad_Ali

Hi,

I apologise if this is an obvious question, but I have tried lots of variations but I still can't get what I want. I want to get all folders of a certain name which do not have a certain group applied to them. We have SQL Server and I'm trying rightid != 1234 but it doesn't give me what I want.

Any ideas?

Thanks

Find more posts tagged with

Comments

Bhupinder_Singh

Could you post the entire SQL query you've put together so far?

- Bhupinder

Imad_Ali

I've written something along the lines of:

select d.dataid

from dtreeacl dt, dtree d

Inner join dtree d2 on d.parentid = d2.dataid
Inner join dtree d3 on d2.parentid = d3.dataid
Inner join dtree d4 on d3.parentid = d4.dataid
Inner join dtree d5 on d4.parentid = d5.dataid

where (d.name IN ('test')
AND d5.name = 'Folders')
and dt.dataid = d.dataid
and dt.Rightid != 1234
and d.subtype = 0

It doesnt give me a list of everything that DOESN'T have this group.

Colin_Schmidt

Try something like this with or without the concatenation. It's possible that the other query failed because there are multiple records in the DTREEACL for a single object and even though one of those records might have that value, others wouldn't....for that DATAID.

select d5.name+' | '+d4.name+' | '+d3.name+' | '+d2.name+' | '+dt.dataid

from dtree dt

join dtree d2 on dt.parentid = d2.dataid

join dtree d3 on d2.parentid = d3.dataid

join dtree d4 on d3.parentid = d4.dataid

join dtree d5 on d4.parentid = d5.dataid and lower(d5.name) = 'folders'

where lower(dt.name) = 'test' and dt.subtype = 0

and dt.dataid <> (select dta.dataid from dtreeacl dta where dt.dataid = dta.dataid and dta.Rightid = 1234)

Colin J.

On Thu, May 7, 2015 at 7:24 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

LiveReport of all folders without a certain group applied Posted byimad.ali@ubs.com (Ali, Imad)On 05/07/2015 07:23 AM

I've written something along the lines of:select d.dataidfrom dtreeacl dt, dtree dInner join dtree d2 on d.parentid = d2.dataid
Inner join dtree d3 on d2.parentid = d3.dataid
Inner join dtree d4 on d3.parentid = d4.dataid
Inner join dtree d5 on d4.parentid = d5.dataidwhere (d.name IN ('test')
AND d5.name = 'Folders')
and dt.dataid = d.dataid
and dt.Rightid != 1234
and d.subtype = 0It doesnt give me a list of everything that DOESN'T have this group.
[To post a comment, use the normal reply function]
Topic: LiveReport of all folders without a certain group applied
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.

Lindsay_Davies

Hi Imad,

Looks like you are struggling here.

Every object has at least 4 DTreeACL records.

You have not said what info you need, so I am not sure why you might be concatenating a fixed number of parent/ancestor names without knowing how many there are.

Are you using SQL server or Oracle? I assume SQL server based on the clues in the suggested queries so far.

My initial suggestion would be along the following lines.

Identify the items which match the folder and name criteria

And take away the items which match the folder and name criteria and do have the group applied to at least one ACL row.

e.g.

select OwnerID, ParentID, DataID, Name from DTree where SubType=0 and Name =’TEST’

minus

select D.OwnerID, D.ParentID, D.DataID, Name from DTree D, DTreeACL A where D.DataID = A.DataID and SubType=0 and Name =’TEST’ and A.RightID = 123456

If you want more path information then you want to just output DataID from the two parts of the query above and then use that list to build the data you need for an outer query or common table expression.

FYI, I tested this query against valid data in an Oracle DB but I do not think there is anything in it that would not work in SQL server. The implicit join should work, rather than write it as an inner join on… etc.

Regards

Lindsay

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 07 May 2015 14:00
To: eLink Recipient
Subject: Re LiveReport of all folders without a certain group applied

Re LiveReport of all folders without a certain group applied

Posted bycolin.schmidt@mwv.com (Schmidt, Colin) On 05/07/2015 08:58 AM

select d5.name+' | '+d4.name+' | '+d3.name+' | '+d2.name+' | '+dt.dataid

from dtree dt

join dtree d2 on dt.parentid = d2.dataid

join dtree d3 on d2.parentid = d3.dataid

join dtree d4 on d3.parentid = d4.dataid

join dtree d5 on d4.parentid = d5.dataid and lower(d5.name) = 'folders'

where lower(dt.name) = 'test' and dt.subtype = 0

and dt.dataid <> (select dta.dataid from dtreeacl dta where dt.dataid = dta.dataid and dta.Rightid = 1234)

Colin J.

On Thu, May 7, 2015 at 7:24 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

LiveReport of all folders without a certain group applied

Posted byimad.ali@ubs.com (Ali, Imad) On 05/07/2015 07:23 AM

I've written something along the lines of:
select d.dataid
from dtreeacl dt, dtree d
Inner join dtree d2 on d.parentid = d2.dataid
Inner join dtree d3 on d2.parentid = d3.dataid
Inner join dtree d4 on d3.parentid = d4.dataid
Inner join dtree d5 on d4.parentid = d5.dataid
where (d.name IN ('test')
AND d5.name = 'Folders')
and dt.dataid = d.dataid
and dt.Rightid != 1234
and d.subtype = 0
It doesnt give me a list of everything that DOESN'T have this group.
[To post a comment, use the normal reply function]
Topic:
LiveReport of all folders without a certain group applied
Forum:
Content Server LiveReports Forum
Content Server:
Knowledge Center

[To post a comment, use the normal reply function]
Topic:	LiveReport of all folders without a certain group applied
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Imad_Ali

Hi Lindsay,

It's been a while . I hope you're well.

I want to get a list of folders that don't have a particular group assigned to them. I was thinking along the lines of what you've sent me and what Colin wrote, but it's still not giving me what I want. Not sure what's happening.

We're on SQL Server.

Thanks,
Imad