Report to list all documents that have been added or a version added.

I have the following LiveReport that I have user inputs for Container (folder), a From Date and a To Date. What I need is only to pull up documents that have been added or a version added during that time frame. It is pulling up all of the documents that have been modified regardless if the document itself has had a new versions added.select name,dataid,subtype,level from dtree where ModifyDate >%2 AND ModifyDate <%3 and %4 and %5 connect by prior dataid=parentid start with dataid=%1Thank you for your help.

Find more posts tagged with

Comments

Martin_Gäckler

You need a join from dtree to dversdata and check dversdata.vercdate instead of dtree.modifydateHope this helps

Mary_Slane_Corona

MartinI changed the SQL to readSELECT name,dataid,subtype,level FROM dtree, dversdata v WHERE v.vercdate >%2 AND v.vercdate <%3 and %4 and %5 CONNECT BY PRIOR dataid=parentid START WITH dataid=%1When I run the LiveReport, I get the following error:Livelink Error: Error processing request. [ORA-30928: Connect by filtering phase runs out of temp tablespace - SELECT name,dataid,subtype,level FROM dtree, dversdata v WHERE v.vercdate >:A1 AND v.vercdate <:A2 and (1=1) and (DTree.SubType=:A3) CONNECT BY PRIOR dataid=parentid START WITH dataid=:A4]I am attaching a doc that shows a screenprint of my LiveReport. Thank you for your help.

Martin_Gäckler

Mary,this is a matrix not a join. Try this:SELECT name,dataid,subtype,level FROM dtree, dversdata v WHERE v.vercdate >:A1 AND v.vercdate <:A2 and (dtree.dataid=dversdata.docid) and (DTree.SubType=:A3) CONNECT BY PRIOR dataid=parentid START WITH dataid=:A4

Mary_Slane_Corona

With your help and help from my friend, John Simon, the LiveReport is working well. Thank you both.

Mary_Slane_Corona

Following is the SQL code used for the report if anyone else needs the same information pulled from Livelink. I am also attaching a screen shot of my LiveReport info.SELECT a.name, a.dataid, a.subtype, a.createdate, a.modifydate, b.vercdate FROM dtree a, dversdata b WHERE a.dataid = b.docid AND a.versionnum = b.version AND a.dataid in (SELECT dataid FROM dtree WHERE subtype = 144 CONNECT BY PRIOR dataid=parentid START WITH dataid=%1) Order by a.name