Report on Files created within a Project & subproject

Cheryl_Henry_(nswcdahladmin_-_(deleted))

I have been tasked to create a report that will list all documents within a project that have been created within a certain time frame. But here's the problem. The main project has sub-projects and the sub-projects have additional sub-projects. Yes, I can do an advanced search, but the user is requesting a report that will only include document name and creator of the document.Here's an example of the SQL (Oracle) that I have thus far:select name, createdate, SUBTYPE,PARENTID, dataidfrom LIVELINK.dtreeWHERE CREATEDATE > '06-DEC-2001'CONNECT BY PRIOR DATAID = -PARENTIDSTART WITH PARENTID = 276923UNION allselect name, createdate, SUBTYPE,PARENTID, dataidfrom LIVELINK.dtreeWHERE CREATEDATE > '06-DEC-2001'CONNECT BY PRIOR DATAID = PARENTIDSTART WITH PARENTID = -276923This only brings back documents that are in the main project level and documents in sub-projects that are one level deep.Any help will be greatly appreciated.

Find more posts tagged with

Comments

eLink User

Message from Alex Kowalenko via eLinkTry "connect by prior dataid = abs(parentid)"-----Original Message-----From: eLink Discussion: Livelink LiveReports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]Sent: Wednesday, September 11, 2002 11:18To: eLink RecipientSubject: Report on Files created within a Project & subprojectReport on Files created within a Project & subprojectPosted by NSWCDahlAdmin on 09/11/2002 11:14 AMI have been tasked to create a report that will list all documents within aproject that have been created within a certain time frame. But here's theproblem. The main project has sub-projects and the sub-projects haveadditional sub-projects. Yes, I can do an advanced search, but the user isrequesting a report that will only include document name and creator of thedocument.Here's an example of the SQL (Oracle) that I have thus far:select name, createdate, SUBTYPE,PARENTID, dataidfrom LIVELINK.dtreeWHERE CREATEDATE > '06-DEC-2001'CONNECT BY PRIOR DATAID = -PARENTIDSTART WITH PARENTID = 276923UNION allselect name, createdate, SUBTYPE,PARENTID, dataidfrom LIVELINK.dtreeWHERE CREATEDATE > '06-DEC-2001'CONNECT BY PRIOR DATAID = PARENTIDSTART WITH PARENTID = -276923This only brings back documents that are in the main project level anddocuments in sub-projects that are one level deep.Any help will be greatly appreciated.[To reply to this thread, use your normal e-mail reply function.]============================================================Discussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink Server:https://knowledge.opentext.com/knowledge/livelink.exe

Cheryl_Henry_(nswcdahladmin_-_(deleted))

Here is my new sql. However, it takes about 1/2 to run through sql*plus. This is too long to run through the Livelink interface. Any help will be greatly appreciated.select name, createdate, SUBTYPE,PARENTID, dataidfrom LIVELINK.dtreeWHERE CREATEDATE > '06-DEC-2001'and subtype = 144CONNECT BY PRIOR DATAID = abs(PARENTID)START WITH PARENTID = 276923UNION allselect name, createdate, SUBTYPE,PARENTID, dataidfrom LIVELINK.dtreeWHERE CREATEDATE > '06-DEC-2001'and subtype = 144CONNECT BY PRIOR DATAID = PARENTIDSTART WITH PARENTID = -276923

eLink User

Message from Mohsin Jessa via eLinkHi,How long is it taking, 1/2 an hour or 1/2 a min ?The culprit is the abs function. Basically that disables the index on thatcol which means you are doing a full tablescan of dtree. How many rows doyou have in this table anyway ?One of the ways you can confirm my theory is to break up the combined sqlinto individual components and verify that the top component probably takesmost of the time and the bottom component of the sql (the one below theunion all) takes much less time.or you can post the explain plan of the individual components as well as thecombined sql and I'll take a look at it.One of the choice you have is to create a function based index on thatcolumn. Let me know if you need help creating that and I'll send you thesql.Hope this helps.Mohsin> -----Original Message-----> From: eLink Discussion: Livelink LiveReports Discussion> [mailto:livereportsdiscussion@elinkkc.opentext.com]> Sent: Monday, September 16, 2002 10:14 AM> To: eLink Recipient> Subject: Here is my new sql. However, it takes about 1/2 to run through> sql*plus. This...>>> Here is my new sql. However, it takes about 1/2 to run through> sql*plus. This...> Posted by NSWCDahlAdmin on 09/16/2002 10:11 AM>> Here is my new sql. However, it takes about 1/2 to run through> sql*plus. This is too long to run through the Livelink> interface. Any help will be greatly appreciated.>> select name, createdate, SUBTYPE,PARENTID, dataid> from LIVELINK.dtree> WHERE CREATEDATE > '06-DEC-2001'> and subtype = 144> CONNECT BY PRIOR DATAID = abs(PARENTID)> START WITH PARENTID = 276923> UNION all> select name, createdate, SUBTYPE,PARENTID, dataid> from LIVELINK.dtree> WHERE CREATEDATE > '06-DEC-2001'> and subtype = 144> CONNECT BY PRIOR DATAID = PARENTID> START WITH PARENTID = -276923>> [To reply to this thread, use your normal e-mail reply function.]>> ============================================================>> Topic: Report on Files created within a Project & subproject> https://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2842613&objAction=viewDiscussion: Livelink LiveReports Discussionhttps://knowledge.opentext.com/knowledge/livelink.exe?func=ll&objId=2249677&objAction=viewLivelink Server:https://knowledge.opentext.com/knowledge/livelink.exe

Cheryl_Henry_(nswcdahladmin_-_(deleted))

I've created a function based index with the following sql create index livelink.parent_child on livelink.dtree(abs(parentid)) storage (initial 1M next 1M pctincrease 0);This does not seem to improve the performance. My initial sql script is still running after about 15 minutes. This still will not work through the Livelink LiveReport interface. This will definitely timeout.Help!

Brad_Bosley_(bradbosley_-_(deleted))

Cheryl,Try this. Depending on the number of projects in your system this may work. Basically you first crawl UP the tree of each project to see if it is under the path you care about. If so then you crawl back down the project to get the documents.select name, createdate, SUBTYPE,PARENTID, dataid from dtreewhere CREATEDATE > '06-DEC-2001' START WITH dataid IN(SELECT -dataid FROM DTREE a WHERE SUBTYPE =202 AND EXISTS (SELECT dataid FROM DTREE WHERE DATAID=276923 START WITH dataid=a.dataid CONNECT BY PRIOR ABS(parentid)=dataid))CONNECT BY PRIOR dataid=parentidRegards,Brad BosleyMotorola

Cheryl_Henry_(nswcdahladmin_-_(deleted))

Brad,I thank you thank you thank you...This worked great!!! It only took about 20 seconds to run.Again thanks so much...Cheryl