Documents with no category 2

How can I find documents that don't have a category? We use SQL Server. Documents are inside of folders that are 3 levels deep from the Enterprise workspace.

Find more posts tagged with

Comments

Colin_Schmidt

That’s a reverse of finding one that does, but more costly SQL running-wise.

You’d check if the DataID did not exist in LLATTRDATA.

Select dt.dataid, dt.name

From dtree dt

Where dt.dataid NOT EXISTS (select lla.id from llattrdata lla where lla.id = dt.dataid)

There’s other variations. I don’t think there are any DAUDITNEW activities that would reveal anything any quicker.

Colin J

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Monday, June 25, 2018 11:47 AM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: Documents with no category 2

Documents with no category 2

Posted bycwagner@mersofmich.com (Wagner, Chantal) On 06/25/2018 01:45 PM

How can I find documents that don't have a category? We use SQL Server. Documents are inside of folders that are 3 levels deep from the Enterprise workspace.

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

This e-mail and any files transmitted with it are for the sole use of the intended recipient(s) and may contain confidential and privileged information. If you are not the intended recipient(s), please reply to the sender and destroy all copies of the original message. Any unauthorized review, use, disclosure, dissemination, forwarding, printing or copying of this email, and/or any action taken in reliance on the contents of this e-mail is strictly prohibited and may be unlawful. Where permitted by applicable law, this e-mail and other e-mail communications sent to and from Cognizant e-mail addresses may be monitored.

Chantal_Wagner

Thanks for the reply. I tried that but it still didn't give me the results I was looking for. I saw another post that suggested a query, but it was for Oracle. How would I do this for SQL server?

--get a list of all documents from parent level through all sub-levels
select distinct d.dataid, d.name, sys_connect_by_path(d.name,':') Path
from dtreecore d
where d.subtype = 144
start with d.parentid = 84308
connect by prior d.dataid = d.parentid

minus

--subtract out all documents that have the category
select distinct d.dataid, d.name ContractName, sys_connect_by_path(d.name,':') Path
from dtreecore d
left outer join llattrdata a on d.dataid = a.id
                       and d.versionnum = a.vernum
where d.subtype = 144
and (a.defid = 84724
and (a.vernum = (select max(a2.vernum)
                      from llattrdata a2
                      where a.id = a2.id)
                  )
and (a.defvern = (select max(a3.defvern)
                      from llattrdata a3
                      where a.id = a3.id)
                   )
start with d.parentid = 84308
connect by prior d.dataid = d.parentid
order by path
;

John Simon

Select dataid from dtree where dataid not in (select id from llattrdata)

Cheers...