Listing of Deleted User Personal Workspaces with sizes

I am wondering if another set of eyes can point why my query doesn't return any data and/or someone can assist me with combing these two queries with a JOIN statement?

/*Query1-Lists all Deleted User Personal Workspaces

SELECT D.DATAID AS "DATAID", D.NAME AS "OBJECT NAME", K.Name AS "EID", K.LASTNAME AS "LASTNAME", K.FIRSTNAME AS "FIRSTNAME", get_groupname(K.groupid) AS "BASEGROUP"

FROM DTREE D, DTREEACL DA ,KUAF K

WHERE DA.DATAID=D.DATAID AND K.ID(+) = DA.RIGHTID AND K.DELETED = 1 AND DA.ACLTYPE IN (1,2) AND k.TYPE=0 AND D.SUBTYPE =142

/*Query2-Calculates the Size of Deleted User Personal Workspaces

select k.id, k.name, k.firstname, k.lastname, SUM((dv.datasize/(1024*1024))/1024)"Size in GB"

from kuaf k, dtree d, dversdata dv

where k.groupid is not null and k.deleted = 1 and d.ownerid = -(k.id) and d.parentid >= k.id and d.dataid = dv.docid

group by k.id, k.name, k.firstname, k.lastname order by SUM((dv.datasize/(1024*1024))/1024) desc

Basically I need a query that Lists all of the Deleted User Personal Workspaces and includes the total size of their workspaces, so the columns returned would be something like:

D.DATAID AS "DATAID"
D.NAME AS "OBJECT NAME"
K.Name AS "EID"
K.LASTNAME AS "LASTNAME"
K.FIRSTNAME AS "FIRSTNAME"
get_groupname(K.groupid) AS "BASEGROUP”
SUM((dv.datasize/(1024*1024))/1024)"Size in GB"

I have been trying to do this with AND statements, but I have been unsuccessful… This is what I have tried so far:

select k.id AS "DATAID", d.name AS "OBJECT NAME", K.Name AS "EID", k.lastname AS "LASTNAME", k.firstname AS "FIRSTNAME", get_groupname(K.groupid) AS "BASEGROUP", SUM((dv.datasize/(1024*1024))/1024)"Size in GB"

from kuaf k, dtree d, dversdata dv

where d.ownerid = -(k.id) and d.parentid >= k.id and d.dataid = dv.docid

and k.id in (SELECT D.DATAID FROM DTREE D, DTREEACL DA ,KUAF K WHERE DA.DATAID=D.DATAID AND K.ID(+) = DA.RIGHTID AND K.DELETED = 1 AND DA.ACLTYPE IN (1,2) AND k.TYPE=0 AND D.SUBTYPE =142)

group by k.id, d.name, k.name, k.firstname, k.lastname, k.groupid order by SUM((dv.datasize/(1024*1024))/1024) desc

As you know, it doesn’t much matter where I get DATAID, EID, LASTNAME, or FIRSTNAME from as they are both available from the DTree and Kuaf tables.

Any assistance you might provide would be greatly appreciated!

Find more posts tagged with

Comments

Colin_Schmidt

Do you want each item within the PWS? If you only want the total space for the User, try this:

select k.name "User", k.lastname||', '||k.firstname "FullName", k2.name "BaseGroup",

to_char(SUM((dv.datasize/(1024*1024))/1024), '999,999.999999') "Size in GB"

from kuaf k

join dtree dt on k.id = abs(dt.ownerid)

join dversdata dv on dt.dataid = dv.docid

join kuaf k2 on k.groupid = k2.id

where k.deleted = 1 and k.type = 0

Group by k.name, k.lastname||', '||k.firstname, k2.name

Colin J.

On Fri, Mar 6, 2015 at 11:04 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Listing of Deleted User Personal Workspaces with sizes Posted bywalter.ruppar@capitalone.com (Ruppar, Walt)On 03/06/2015 11:00 AM

I am wondering if another set of eyes can point why my query doesn't return any data and/or someone can assist me with combing these two queries with a JOIN statement?/*Query1-Lists all Deleted User Personal WorkspacesSELECT D.DATAID AS "DATAID", D.NAME AS "OBJECT NAME", K.Name AS "EID", K.LASTNAME AS "LASTNAME", K.FIRSTNAME AS "FIRSTNAME", get_groupname(K.groupid) AS "BASEGROUP"FROM DTREE D, DTREEACL DA ,KUAF KWHERE DA.DATAID=D.DATAID AND K.ID(+) = DA.RIGHTID AND K.DELETED = 1 AND DA.ACLTYPE IN (1,2) AND k.TYPE=0 AND D.SUBTYPE =142/*Query2-Calculates the Size of Deleted User Personal Workspacesselect k.id, k.name, k.firstname, k.lastname, SUM((dv.datasize/(1024*1024))/1024)"Size in GB" from kuaf k, dtree d, dversdata dvwhere k.groupid is not null and k.deleted = 1 and d.ownerid = -(k.id) and d.parentid >= k.id and d.dataid = dv.docidgroup by k.id, k.name, k.firstname, k.lastname order by SUM((dv.datasize/(1024*1024))/1024) desc Basically I need a query that Lists all of the Deleted User Personal Workspaces and includes the total size of their workspaces, so the columns returned would be something like: D.DATAID AS "DATAID" D.NAME AS "OBJECT NAME" K.Name AS "EID" K.LASTNAME AS "LASTNAME" K.FIRSTNAME AS "FIRSTNAME" get_groupname(K.groupid) AS "BASEGROUP” SUM((dv.datasize/(1024*1024))/1024)"Size in GB" I have been trying to do this with AND statements, but I have been unsuccessful… This is what I have tried so far:select k.id AS "DATAID", d.name AS "OBJECT NAME", K.Name AS "EID", k.lastname AS "LASTNAME", k.firstname AS "FIRSTNAME", get_groupname(K.groupid) AS "BASEGROUP", SUM((dv.datasize/(1024*1024))/1024)"Size in GB" from kuaf k, dtree d, dversdata dvwhere d.ownerid = -(k.id) and d.parentid >= k.id and d.dataid = dv.docidand k.id in (SELECT D.DATAID FROM DTREE D, DTREEACL DA ,KUAF K WHERE DA.DATAID=D.DATAID AND K.ID(+) = DA.RIGHTID AND K.DELETED = 1 AND DA.ACLTYPE IN (1,2) AND k.TYPE=0 AND D.SUBTYPE =142)group by k.id, d.name, k.name, k.firstname, k.lastname, k.groupid order by SUM((dv.datasize/(1024*1024))/1024) desc As you know, it doesn’t much matter where I get DATAID, EID, LASTNAME, or FIRSTNAME from as they are both available from the DTree and Kuaf tables. Any assistance you might provide would be greatly appreciated!
[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.

Walt_Ruppar

Thanks Colin!