Display workspaces of specific category defid with zero documents

Does anyone have an example where the we need to display the workspaces that have no documents in the workspace folders. As eventually the empty workspaces must be deleted. As using childcount=0 or group by having count(*)=0 did not work

Please suggest

Find more posts tagged with

Comments

Appu Nair

In your other posting remember https://forums.opentext.com/forums/support/discussion/comment/949015#Comment_949015 I told you there is a very easy way to find all the Objects of Type Workspace.

SELECT TOP (1000) [OwnerID],[ParentID],[DataID],[Name],[SubType] FROM [dbo].[DTree] where OwnerID=929374 and ParentID <> -1;

So you can extrapolate that knowledge.

SELECT TOP (1000) [OwnerID],[ParentID],[DataID],[Name],[SubType] FROM [DTree]where SubType=849 /*I want only WKSpace types*/ and ParentID = -1 and DataId <>-23002 /*I do not want to look at my template*/;

The above query is giving me all the Objects of Type Workspace and I am excluding the template which is identified by -23002 I looked at that in GUI if needed it can be written without hardcoding...

Once you have DataId's of the Workspace we can use DTreeAncestors effectively

select DT.DataID,DT.Name,DT.SubType,DT.ChildCount from DTree DT,DTreeAncestors DTA where DT.DataID=DTA.AncestorID and DT.OwnerID in (SELECT -[DataID] FROM [DTree] where SubType=849 /*I want only WKSpace types*/ and ParentID = -1 and DataId <>-23002) order by ParentID,ChildCount desc

Use above query to only analyze the task...

All that query is doing is it is saying List all Objects in each workspace and BTW can you tell me if you have ChildCounts somewhere.

ChidCounts are very notorious for going wrong so prefer to analyze the output using Excel and try to do some work

in case you are looking to find a pretty decent SQL without doing analysis like me on excel wait for someone who probably does some group by .

This is the old LR & WR forum before it was ported into the new place look here for all my doubts . It had a wealth of CS minds helping each other

https://knowledge.opentext.com/knowledge/cs.dll?func=ll&objId=27199074&objAction=browse

Sreekanth_Bhojanapu

Hi Appu Nair,

Thank you for your help.

Yes we need to list the workspaces that have zero documents inside them so that those can be deleted. Hence childcount has to be considered here. So trying this by using group by but unable to fetch results here

Tried accessing the other link shared here but that is only in read only mode.. Please suggest

Appu Nair

My intent of posting that link was only to say that in those forums you might find lots of Sql ...

I still find what I need from there afaik I would start writing something along the lines I posted and do some excel manipulation.( I don’t know / have time to further refine query)

Sreekanth_Bhojanapu

Hi All,

select distinct ll.valstr, ll.id, dt.childcount from LLAttrData ll, DTreeAncestors da, dtreecore dt

where dt.dataid=da.AncestorID and da.ancestorid=11344 and ll.defid=11791 and ll.attrid=2

group by dt.childcount having dt.childcount = 0

Please suggest on how can i reframe the above statement to display the records that have no documents in the folders of workspaces..

The above query throws an error

Msg 8120, Level 16, State 1, Line 17

Column 'LLAttrData.ValStr' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.