I want a report only to include documents in a specific project/folder

Hi everybody,I have created a live report, and I am very happy that it basicly works. The problem is, that the report is very database-inefficient, because it is looking for category attributes for all documents in LL. But I do not manage to restict the search only for documents within a certain project/folder.The report looks as follows:select a.dataid, a.parentid, a.subtype, a.name, a.Document_Number, b.Title, c.Actual_Revision, d.Actual_status from (select llattrdata.id as dataid, dtree.parentid, dtree.subtype, dtree.name, llattrdata.defid, llattrdata.ValStr as Document_Number from llattrdata, dtree where llattrdata.defid = 816881 and llattrdata.attrid = 5 and llattrdata.id = dtree.dataid and llattrdata.vernum = dtree.versionnum) a inner join (select llattrdata.id as dataid, dtree.parentid, dtree.subtype, dtree.name, llattrdata.defid, llattrdata.ValStr as Title from llattrdata, dtree where llattrdata.defid = 816881 and llattrdata.attrid = 9 and llattrdata.id = dtree.dataid and llattrdata.vernum = dtree.versionnum) b on a.dataid = b.dataid inner join (select llattrdata.id as dataid, dtree.parentid, dtree.subtype, dtree.name, llattrdata.defid, llattrdata.ValStr as Actual_Revision from llattrdata, dtree where llattrdata.defid = 57981 and llattrdata.attrid = 2 and llattrdata.id = dtree.dataid and llattrdata.vernum = dtree.versionnum) c inner join (select llattrdata.id as dataid, dtree.parentid, dtree.subtype, dtree.name, llattrdata.defid, llattrdata.ValStr as Actual_status from llattrdata, dtree where llattrdata.defid = 57981 and llattrdata.attrid = 7 and llattrdata.id = dtree.dataid and llattrdata.vernum = dtree.versionnum) d on c.dataid = d.dataid on a.dataid = d.dataid where a.subtype = 144 order by Document_NumberWhat do I have to add where to reduce the search to a specific project/folder (with the object ID of project/folder)???Many thanks for all help in advance!

Find more posts tagged with

Comments

Appu_Nair

It would depend on the database of your livelink with oracle you have connect bya simple examplefind me objects under this id 5858900select * from dtree where subtype=144 CONNECT BY PRIOR dataid = parentid start with dataid= 5858900If its is SQLserver you are out of luck you have to write a CTE or function that emulates the oracle tree walk.Lot of examples in LR area.also dtreeancestors is another table that is supposed to be more effective but I have no experience in doing queries with it,.Another thing is that the connect by will not traverse negative dataid's so you may have to abs(dataid) which will make it longer on query execution.Also I would create a view to that category that will make it much easier to query.

Nicole_Bride

Message from Bride, Nicole <nbride@cvps.com> via eLink

eLink

Dtreeancestors works really well, but I believe it's onlypopulated with Recommender on.

From: eLink Discussion:Open Text Live Reports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Tuesday, September 28, 2010 12:42 PM
To: eLink Recipient
Subject: It would depend on the database of your livelink with oracleyou have connect...

It would depend on the database of your livelink with oracle you have connect...		Posted by anair@alitek.com (Nair, Appu) on 2010/09/28 12:40
In reply to: I want a report only to include documents in a specific project/folder		Posted by torsten.bietz@lurgi.com (Bietz, Torsten M) on 2010/09/28 12:15

It would depend on the database of your livelink with oracle you have connect by

a simple example

find me objects under this id 5858900

select * from dtree where subtype=144 CONNECT BY PRIOR dataid = parentid start with dataid= 5858900

If its is SQLserver you are out of luck you have to write a CTE or function that emulates the oracle tree walk.Lot of examples in LR area.

also dtreeancestors is another table that is supposed to be more effective but I have no experience in doing queries with it,.

Another thing is that the connect by will not traverse negative dataid's so you may have to abs(dataid) which will make it longer on query execution.

Also I would create a view to that category that will make it much easier to query.

[To reply to this thread, use your normal E-mail reply function.]
Topic:	I want a report only to include documents in a specific project/folder
Discussion:	Open Text Live Reports Discussion
Livelink Server:	knowledge-wlweb01

To Unsubscribe from this Discussion, send an e-mail to unsubscribe.livereportsdiscussion@elinkkc.opentext.com.

Torsten_Bietz

Sorry, I forgot to mention, that we use Oracle...I have tried your suggestion, and another two more questions:1) When I use your select statement as it is, it works fine for folders, but not for a project object. When the dataid is the object id of a project, the query returns no results. Is there a possibility to also query only objects, which are underneeth a project?2) I tried to implement the "connect by prior" part into my sql statement, and gave an dataid of a folder. But also this did not return any results. The whole query looks like this:select a.dataid, a.parentid, a.subtype, a.name, a.Document_Number, b.Title, c.Actual_Revision, d.Actual_status from (select llattrdata.id as dataid, dtree.parentid, dtree.subtype, dtree.name, llattrdata.defid, llattrdata.ValStr as Document_Number from llattrdata, dtree where llattrdata.defid = 816881 and llattrdata.attrid = 5 and llattrdata.id = dtree.dataid and llattrdata.vernum = dtree.versionnum) a inner join (select llattrdata.id as dataid, dtree.parentid, dtree.subtype, dtree.name, llattrdata.defid, llattrdata.ValStr as Title from llattrdata, dtree where llattrdata.defid = 816881 and llattrdata.attrid = 9 and llattrdata.id = dtree.dataid and llattrdata.vernum = dtree.versionnum) b on a.dataid = b.dataid inner join (select llattrdata.id as dataid, dtree.parentid, dtree.subtype, dtree.name, llattrdata.defid, llattrdata.ValStr as Actual_Revision from llattrdata, dtree where llattrdata.defid = 57981 and llattrdata.attrid = 2 and llattrdata.id = dtree.dataid and llattrdata.vernum = dtree.versionnum) c inner join (select llattrdata.id as dataid, dtree.parentid, dtree.subtype, dtree.name, llattrdata.defid, llattrdata.ValStr as Actual_status from llattrdata, dtree where llattrdata.defid = 57981 and llattrdata.attrid = 7 and ValStr != '?' and llattrdata.id = dtree.dataid and llattrdata.vernum = dtree.versionnum) d on c.dataid = d.dataid on a.dataid = d.dataid where a.subtype = 144 CONNECT BY PRIOR a.dataid = a.parentid start with a.dataid= 813797 order by Document_NumberAny ideas are very welcome...!!!

Appu_Nair

if it is a livelink project object then as I said doing abs on the parentid will get you going.as an egselect * from dtree CONNECT BY PRIOR dataid = abs(parentid) start with dataid= 312600this will be terribly inefficient because abs negates indexes and it will crawl.notice i removed dtree.subtype=144 because projects subtype=202 and i want everything underneath thatSorry on your 2nd qn I do not understand that query it is too bigso I usually create a view for reporting on category then I can do away with the joins inners/outers .Also I would check an oracle site to see if order by will work with connect by clause or not

Hiromi_Byrd

(1) Items in a ProjectIf it is a report only to include Documents in a Project only, you can also specify "OwnerID" instead of using "CONNECT BY PRIOR dataid=abs (parentid)." Every item in a Project has the same OwnerID whether it is right under the Project or it is in a sub-folder in the Project. So you can replace CONNECT BY PRIOR dataid = abs(parentid) start with dataid= 312600withOwnerID=12345 and subtype = 144\In my experience, performance (time to return results) is better when I use OwnerID to report on the items in a Project. ******************************************(2) ORDER BY and CONNECT BYIn my experience, ORDER BY works with CONNECT BY on the oracle server. HB