Oracle SQL Report to identify items with more than 5 versions within a container

Hi there,

We have started to put together a Oracle SQL report to identify all items within our environment which have greater than 5 versions; we want to be able to do this by specifying a folder and getting results for this folder and all subfolders. The below SQL is what we have come up with. In this instance dataid 42412 is the id of the folder we want the report to run against.

If we remove the last line "start with dataid = 42412 connect by nocycle prior dataid = parentid;" the report runs however doesn't allow us to specify a folder and instead returns results for entire environment. When we put this final line in to specify a folder the report runs for a very long time and appears to get stuck in a loop, sometimes it'll timeout before we stop it.

Any suggestions on what we're doing wrong?

CREATE OR REPLACE FUNCTION Folder_Path ( selid number ) return varchar2 AS

     n integer;
     pname varchar2(255);
     pstring varchar2(32000);
     cursor plist (id number) is
       select name
       from dtreecore
       where dataid > 0 and level > 1
       start with dataid = id
       connect by abs(dataid) = prior abs(parentid)
       order by level desc;
   BEGIN
     n := 0;
     pstring := '';
     open plist(selid);
     LOOP
       n := n + 1;
       BEGIN
         fetch plist into pname;
       EXCEPTION
         when value_error then return 'ERROR: Cannot find folder path.';
       END;
       exit when plist%NOTFOUND;
       IF n = 1 THEN pstring := pname;
       ELSE pstring := pstring || '/' || pname;
       END IF;
     END LOOP;
     close plist;
     return pstring;
   END Folder_Path;
/

select
      distinct RPAD(dtreecore.name,50, ' ') as "File Name",
      (select RPAD (kuaf.name,30, ' ') from kuaf where kuaf.id=dtreecore.userid) as "Owner Username",
      Folder_Path(dtreecore.dataid) as "Folder Path"
FROM dtreecore
where dtreecore.versionnum>5
start with dataid = 42412 connect by nocycle prior dataid = parentid;

Find more posts tagged with

Comments

eLink User

select
      distinct RPAD(dtreecore.name,50, ' ') as "File Name",
      (select RPAD (kuaf.name,30, ' ') from kuaf where kuaf.id=dtreecore.userid) as "Owner Username",
      Folder_Path(dtreecore.dataid) as "Folder Path"
FROM dtreecore
where dtreecore.versionnum>5 and dtreecore.subtype=144
start with dataid = 42412 connect by nocycle prior dataid = parentid;

Does this run faster ? Can you run this in SQLdeveloper or TOAD? the red colored things are a filter for documents so Oracle will not attempt all objects...

If it does not run faster remove your function and then run.Read Lindsay davies's many many replies in this forum saying why you want to stay away for abs(something) because that negates the index.He has shown the correct way of handling negative dataids.

I also attach our function restricted to 4000 chars may not get all of what you want but may be a start to see if it is any better.

***********************************

CREATE OR REPLACE FUNCTION <schema>.dtree_obj_path(p_val IN NUMBER)

RETURN VARCHAR2

l_result VARCHAR2(4000);

CURSOR sov1 is

SELECT name FROM dtree

start with dataid = p_val

connect by prior parentid=dataid;

BEGIN

FOR i IN sov1 LOOP

l_result := i.name||':'||l_result;

END LOOP;

RETURN l_result;

EXCEPTION

WHEN OTHERS THEN

l_result := 'Return Value Longer than 4000 Bytes';

END;

*****************************************************************************

..appu appnair appunair

Well, if I called the wrongnumber, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937

On Thu, Dec 3, 2015 at 5:22 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Oracle SQL Report to identify items with more than 5 versions within a container Posted byMcCann, ChrisOn 12/03/2015 06:17 AM

Hi there,We have started to put together a Oracle SQL report to identify all items within our environment which have greater than 5 versions; we want to be able to do this by specifying a folder and getting results for this folder and all subfolders. The below SQL is what we have come up with. In this instance dataid 42412 is the id of the folder we want the report to run against.If we remove the last line "start with dataid = 42412 connect by nocycle prior dataid = parentid;" the report runs however doesn't allow us to specify a folder and instead returns results for entire environment. When we put this final line in to specify a folder the report runs for a very long time and appears to get stuck in a loop, sometimes it'll timeout before we stop it.Any suggestions on what we're doing wrong?CREATE OR REPLACE FUNCTION Folder_Path ( selid number ) return varchar2 AS     n integer;
     pname varchar2(255);
     pstring varchar2(32000);
     cursor plist (id number) is
       select name
       from dtreecore
       where dataid > 0 and level > 1
       start with dataid = id
       connect by abs(dataid) = prior abs(parentid)
       order by level desc;
   BEGIN
     n := 0;
     pstring := '';
     open plist(selid);
     LOOP
       n := n + 1;
       BEGIN
         fetch plist into pname;
       EXCEPTION
         when value_error then return 'ERROR: Cannot find folder path.';
       END;
       exit when plist%NOTFOUND;
       IF n = 1 THEN pstring := pname;
       ELSE pstring := pstring || '/' || pname;
       END IF;
     END LOOP;
     close plist;
     return pstring;
   END Folder_Path;
/select
      distinct RPAD(dtreecore.name,50, ' ') as "File Name",
      (select RPAD (kuaf.name,30, ' ') from kuaf where kuaf.id=dtreecore.userid) as "Owner Username",
      Folder_Path(dtreecore.dataid) as "Folder Path"
FROM dtreecore
where dtreecore.versionnum>5
start with dataid = 42412 connect by nocycle prior dataid = parentid;
[To post a comment, use the normal reply function]
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

John Simon

Chris,

Querying Dtree will not tell you how many versions of a document there are. A document could be on version 12 but only have 1 version out there (the rest have been deleted/purged). You want to query DVersData and do a count. I do not have Oracle in front of me but something like select dataid, count(*) from dversdata having count(*) > 5 group by dataid. If you need to check within a specific folder you will have to add that in (which I would do with a join to DTreeAncestors...depending on which version of LL you are using).

Cheers...

Chris McCann

Thank you for taking the time to respond :-)

Fair point on the use of abs() and restricting to only search document subtypes, have been following much of Lindsay Davies's posts however new to SQL so still finding my feet and appreciate the feedback.

Also good point on Dtree and version number, actually knew this (and had forgotten for the moment!) but was saving amending it for when the rest of the query operated as expected. Which as it turns out is now as the below is returning prompt results in Oracle SQL Developer.

Suspect I'm doing something really silly here with the count (*) and hitting a blind spot, all counts are returned as 7 versions regardless of the number of versions. If I change the count(*)>5 to count(*)>4 I get an error saying: "single-row subquery returns more than one row"

CREATE OR REPLACE FUNCTION Folder_Path(p_val IN NUMBER)

RETURN VARCHAR2
IS
l_result VARCHAR2(4000);

CURSOR sov1 is
SELECT name FROM dtree
start with dataid = p_val
connect by prior parentid=dataid;
BEGIN

FOR i IN sov1 LOOP

l_result := i.name||'/'||l_result;

END LOOP;
RETURN l_result;
EXCEPTION
WHEN OTHERS THEN
l_result := 'Return Value Longer than 4000 Bytes';

END;

select
      distinct RPAD(dtreecore.name,50, ' ') as "File Name",
      (select RPAD (kuaf.name,30, ' ') from kuaf where kuaf.id=dtreecore.userid) as "Owner Username",
      dtreecore.dataid,
      (select count(*) from dversdata,dtreecore where dtreecore.dataid = dversdata.docid and dtreecore.subtype = 144
      group by DVersData.MimeType,dtreecore.dataid having count(*)>5) as "Version Count",
      RPAD(Folder_Path(dtreecore.dataid),250,' ') as "Folder Path"
FROM dtreecore
where dtreecore.subtype = 144
start with dataid = 32132 connect by nocycle prior dataid = parentid;

John Simon

Chris,

Not 100% sure as I still don't have Oracle in front of me but I think your trouble is with the line

group by DVersData.MimeType,dtreecore.dataid having count(*)>5) as "Version Count",

I think that will return more than 1 result when you have more than 1 mimetype. You should only group by 1 thing in this case.

Cheers...

Chris McCann

Bang on the money again John, it was counting MimeTypes. Amended however now consistently getting the error: "single-row subquery returns more than one row"

If I copy/paste the subquery into a seperate worksheet it returns a single row of results for version count exactly as expected and with the correct counts.

Thanks for looking!

select
      distinct RPAD(dtreecore.name,50, ' ') as "File Name",
      (select RPAD (kuaf.name,30, ' ') from kuaf where kuaf.id=dtreecore.userid) as "Owner Username",
      dtreecore.dataid,
      (select count(*) from dversdata,dtreecore where dtreecore.dataid = dversdata.docid and dtreecore.subtype = 144
      group by dtreecore.dataid having count(*)>5) as "Version Count",
      RPAD(Folder_Path(dtreecore.dataid),250,' ') as "Folder Path"
FROM dtreecore
where dtreecore.subtype = 144
start with dataid = 32132 connect by nocycle prior dataid = parentid;

eLink User

While I will never be able to write a query as good as John here's my thought.

You may have to structure a temp table to have your items having count > in a temp table. with a dataid

create table z_blimp as (

select count(*) as versioncount,dataid as joindataid from dversdata,dtree where dtree.dataid = dversdata.docid and dtree.subtype = 144

group by dtree.dataid having count(*)>5);

So that table contains all dtree.dataid's with your count criterion

Then if you want a report to limit the dataids to a particular structure you would do something like this

select dataid from dtree dt2 where dt2.subtype=144 start with dataid = 47258 connect by nocycle prior dataid = parentid

That is the list of dataid's that satisfy the limb of what you are reporting

You should probably not use dtreecore then you are also counting objects people deleted

Dtree is a view and it is the table livelink has for about 20 yrs now so now it is called dtreecore

so now one could write the query as a join between two tables

select

distinct RPAD(dt.name,50, ' ') as "File Name",

dt.dataid,ZB.VERSIONCOUNT

FROM dtree dt,z_blimp zb

where dt.subtype = 144

and ZB.JOINDATAID=DT.DATAID

and DT.DATAID in (select dataid from dtree dt2 where dt2.subtype=144 start with dataid = 47258 connect by nocycle prior dataid = parentid);

Note I omitted your niceties like the KUAF and the folder path function that will slow this already da**** slow query.

Also I use table aliases so it is better.

Now you just have to wait for John to write/show it in a better fashion

BTW in the above I am alaready filtering the feeder query for 144 so the dt.subtype is not needed

Where is Colin when the forum needs him?

..appu appnair appunair

Well, if I called the wrongnumber, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937

On Mon, Dec 7, 2015 at 9:14 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Oracle SQL Report to identify items with more than 5 versions within a container Posted byMcCann, ChrisOn 12/07/2015 10:09 AM

Bang on the money again John, it was counting MimeTypes. Amended however now consistently getting the error: "single-row subquery returns more than one row"If I copy/paste the subquery into a seperate worksheet it returns a single row of results for version count exactly as expected and with the correct counts.Thanks for looking!select
      distinct RPAD(dtreecore.name,50, ' ') as "File Name",
      (select RPAD (kuaf.name,30, ' ') from kuaf where kuaf.id=dtreecore.userid) as "Owner Username",
      dtreecore.dataid,
      (select count(*) from dversdata,dtreecore where dtreecore.dataid = dversdata.docid and dtreecore.subtype = 144
      group by dtreecore.dataid having count(*)>5) as "Version Count",
      RPAD(Folder_Path(dtreecore.dataid),250,' ') as "Folder Path"
FROM dtreecore
where dtreecore.subtype = 144
start with dataid = 32132 connect by nocycle prior dataid = parentid;

[To post a comment, use the normal reply function]Topic:Oracle SQL Report to identify items with more than 5 versions within a containerForum:Content Server LiveReports ForumContent Server:Knowledge Center

Colin_Schmidt

I'm here. I was waiting to see what showed. Sometimes I need help too. I wouldn't have approached from this point(creating a table), as I am a novice at Oracle. I just know the tables pretty well. All of queries are very straightforward. They may involve a lot of tables, but I like corroborating data. I just replied to one on Brava Annotations/Markups.

Colin J. Schmidt
Opentext Content Server Support Admin

501 S. 5th St.| Richmond, VA, 23219
T 804.444.5012 | M 918.904.9223
colin.schmidt@westrock.com | www.westrock.com

On Mon, Dec 7, 2015 at 11:44 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Re Oracle SQL Report to identify items with more than 5 versions within a container Posted byeLinkOn 12/07/2015 11:43 AM

While I will never be able to write a query as good as John here's my thought.
You may have to structure a temp table to have your items having count > in a temp table. with a dataid

create table z_blimp as (
select count(*) as versioncount,dataid as joindataid from dversdata,dtree where dtree.dataid = dversdata.docid and dtree.subtype = 144
group by dtree.dataid having count(*)>5);

So that table contains all dtree.dataid's with your count criterion

Then if you want a report to limit the dataids to a particular structure you would do something like this
select dataid from dtree dt2 where dt2.subtype=144 start with dataid = 47258 connect by nocycle prior dataid = parentid

That is the list of dataid's that satisfy the limb of what you are reporting

You should probably not use dtreecore then you are also counting objects people deleted
Dtree is a view and it is the table livelink has for about 20 yrs now so now it is called dtreecore

so now one could write the query as a join between two tables

select
distinct RPAD(dt.name,50, ' ') as "File Name",
dt.dataid,ZB.VERSIONCOUNT
FROM dtree dt,z_blimp zb
where dt.subtype = 144
and ZB.JOINDATAID=DT.DATAID
and DT.DATAID in (select dataid from dtree dt2 where dt2.subtype=144 start with dataid = 47258 connect by nocycle prior dataid = parentid);

Note I omitted your niceties like the KUAF and the folder path function that will slow this already da**** slow query.
Also I use table aliases so it is better.

Now you just have to wait for John to write/show it in a better fashion

BTW in the above I am alaready filtering the feeder query for 144 so the dt.subtype is not needed

Where is Colin when the forum needs him?

..appu appnair appunair

Well, if I called the wrongnumber, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937

On Mon, Dec 7, 2015 at 9:14 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Oracle SQL Report to identify items with more than 5 versions within a container Posted byMcCann, ChrisOn 12/07/2015 10:09 AM

Bang on the money again John, it was counting MimeTypes. Amended however now consistently getting the error: "single-row subquery returns more than one row"If I copy/paste the subquery into a seperate worksheet it returns a single row of results for version count exactly as expected and with the correct counts.Thanks for looking!select
      distinct RPAD(dtreecore.name,50, ' ') as "File Name",
      (select RPAD (kuaf.name,30, ' ') from kuaf where kuaf.id=dtreecore.userid) as "Owner Username",
      dtreecore.dataid,
      (select count(*) from dversdata,dtreecore where dtreecore.dataid = dversdata.docid and dtreecore.subtype = 144
      group by dtreecore.dataid having count(*)>5) as "Version Count",
      RPAD(Folder_Path(dtreecore.dataid),250,' ') as "Folder Path"
FROM dtreecore
where dtreecore.subtype = 144
start with dataid = 32132 connect by nocycle prior dataid = parentid;

[To post a comment, use the normal reply function]Topic:Oracle SQL Report to identify items with more than 5 versions within a containerForum:Content Server LiveReports ForumContent Server:Knowledge Center

[To post a comment, use the normal reply function]
Topic:	Oracle SQL Report to identify items with more than 5 versions within a container
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

This electronic message contains information from WestRockCompany (www.westrock.com) or itssubsidiaries, which may be confidential, privileged or otherwise protected fromdisclosure. The information is intended to be disclosed to and used by only thenamed recipient(s). If you are not the intendedrecipient, then your review, use, disclosure, printing, copying, ordistribution of this message or its contents is prohibited. If you havereceived this message in error, please notify WestRock immediately at postmaster@westrock.com, and delete themessage from your system. Unless previously authorized in writing, thismessage does not constitute an offer, acceptance, or agreement of any kind.Sender is not liable for damage, errors or omissions related to or caused bytransmission of this message. © WestRock Company.

eLink User

Here's a Revised version not using a temp table

select count(*) as versioncount,dataid as joindataid,dtree.name

from dversdata,dtree where dtree.dataid = dversdata.docid and dtree.subtype = 144

and dataid in (select dataid from dtree dt2 where dt2.subtype=144 start with dataid = 47258

connect by nocycle prior dataid = parentid)

group by dtree.dataid,dtree.name having count(*)>5 ;

I did not try the niceties in your report but every piece of info that you are displaying needs

to be something the group by operation has to understand

Not complete for yours but perhaps you can improve

..appu appnair appunair

Well, if I called the wrongnumber, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937

Chris McCann

Just to add I've not forgotten about this by any means however got pulled into something else and attending training next Mon-Wed so it's probably going to be towards end of next week before I can try to pickup and see where I can get to.

Many thanks for the responses :-)

Chris McCann

Promised to return and here I am, been pulled in a few different directions recently. Rather chuffed managed to get together a working version of this report. However I'm quite sure an Oracle wiz would be able to do things better, my end result so far is below:

Added in an owner <>1000 part as our dversdata appears to hold multiple entries for documents which are not the document themself, look to be thumbnail icons. Each time they are added by Admin as owner so this excludes them from version count.

As always any feedback is warmly welcomed :-)

create or replace function Folder_Path(p_val in number)

return varchar2
is
l_result varchar2(4000);

cursor sov1 is
select name from dtree
start with dataid = p_val
connect by prior parentid=dataid;
begin

for i in sov1 loop

l_result := i.name||'/'||l_result;

end loop;
return l_result;
exception
when others then
l_result := 'Return Value Longer than 4000 Bytes';

end;
/

select
      RPAD(dtreecore.name,50, ' ') as "File Name",
      (select RPAD (kuaf.name,30, ' ') from kuaf where kuaf.id=dtreecore.userid) as "Owner Username",
      dtreecore.dataid,
      (select count(1) from dversdata where dversdata.docid=dtreecore.dataid and owner <> 1000) as "Version Count",
      RPAD(Folder_Path(dtreecore.dataid),250,' ') as "Folder Path"
from dtreecore
where dtreecore.subtype = 144 and (select count(1) from dversdata where dversdata.docid=dtreecore.dataid and owner <> 1000)>5
start with dataid = &Folder_ID connect by nocycle prior dataid = parentid;

eLink User

I have not tested the query but it looks like the path function is something I posted.Suggestions for future not a critique

prior parentid=dataid; Livelink has the concept of negative parentid as in many objects other than commonly used ones like folders and documents,say you use livelink projects,compound documents ,template workspaces,binders etc.So keeping them in that relation "will not" crawl those.I like Lindsay Davies's postings where he has a way to avoid using ABS as you will understand that it is not efficient ,so he has shown a better way.
For extreme performance OT has introduced DTREEANCESTORS and DBROWSEANCESTORS which will traverse objects faster than connect by construct.Further if you go work in a company that has a SQLserver(HANA announced and something else in 16) livelink it becomes not db specific.
Typically one should stay away from dtreecore because it will count deleted ones as well. DTREE(a view in new livelink and table in old) for all practical purposes might be better.

..appu appnair appunair

Well, if I called the wrongnumber, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937

On Fri, Jan 15, 2016 at 5:36 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

RE Re Oracle SQL Report to identify items with more than 5 versions within a container Posted byMcCann, ChrisOn 01/15/2016 06:32 AM

Promised to return and here I am, been pulled in a few different directions recently. Rather chuffed managed to get together a working version of this report. However I'm quite sure an Oracle wiz would be able to do things better, my end result so far is below:Added in an owner <>1000 part as our dversdata appears to hold multiple entries for documents which are not the document themself, look to be thumbnail icons. Each time they are added by Admin as owner so this excludes them from version count.As always any feedback is warmly welcomed :-) create or replace function Folder_Path(p_val in number)return varchar2
is
l_result varchar2(4000);cursor sov1 is
select name from dtree
start with dataid = p_val
connect by prior parentid=dataid;
beginfor i in sov1 loopl_result := i.name||'/'||l_result;end loop;
return l_result;
exception
when others then
l_result := 'Return Value Longer than 4000 Bytes';end;
/select
      RPAD(dtreecore.name,50, ' ') as "File Name",
      (select RPAD (kuaf.name,30, ' ') from kuaf where kuaf.id=dtreecore.userid) as "Owner Username",
      dtreecore.dataid,
      (select count(1) from dversdata where dversdata.docid=dtreecore.dataid and owner <> 1000) as "Version Count",
      RPAD(Folder_Path(dtreecore.dataid),250,' ') as "Folder Path"
from dtreecore
where dtreecore.subtype = 144 and (select count(1) from dversdata where dversdata.docid=dtreecore.dataid and owner <> 1000)>5
start with dataid = &Folder_ID connect by nocycle prior dataid = parentid;
[To post a comment, use the normal reply function]
Topic: Oracle SQL Report to identify items with more than 5 versions within a container
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

Chris McCann

The path function is lifted from here, first to admit most of this is cobbled together from snippits here & there as I attempt to become a little more familiar.

1) So far as I can see I'm no longer using ABS unless I'm misunderstanding something (more than likely!)?

2) We had updates to DTREEANCESTORS disabled previously to improve overall DB performance by OpenText so reluctant to use this. It would be a much better way for this report right enough.

3) Fair point, I removed this before after feedback and it's crept back in. Amended dtreecore to dtree in my local version.

Thanks for your response and feedback :-)

eLink User

On your Point #1 I was just saying that depending on your organizations needs there are subtypes in livelink that have negative parentids and they have to be handled differently..On your point #2 only old versions like LL971 and older has problems with dtreeancestors(recommender module is the real culprit).I think now OT code uses dtreeancestors very much in oscript to find objects so if you are on newer versions ask OT whether they would recommend you not use dtreeancestors.

.....appu appnair appunair

Well, if I called the wrongnumber, why did you answer the phone?
James Thurber, New Yorker cartoon caption, June 5, 1937

On Mon, Jan 18, 2016 at 5:20 AM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Re RE Re Oracle SQL Report to identify items with more than 5 versions within a container Posted byMcCann, ChrisOn 01/18/2016 06:16 AM

The path function is lifted from here, first to admit most of this is cobbled together from snippits here & there as I attempt to become a little more familiar.1) So far as I can see I'm no longer using ABS unless I'm misunderstanding something (more than likely!)?2) We had updates to DTREEANCESTORS disabled previously to improve overall DB performance by OpenText so reluctant to use this. It would be a much better way for this report right enough.3) Fair point, I removed this before after feedback and it's crept back in. Amended dtreecore to dtree in my local version.Thanks for your response and feedback :-)
[To post a comment, use the normal reply function]
Topic: Oracle SQL Report to identify items with more than 5 versions within a container
Forum: Content Server LiveReports Forum
Content Server: Knowledge Center

Chris McCann

Ah I see, for point #1 do get what you're saying about negative parentids now ... can take me a while sometimes but usually things do click eventually! I'll have a think about this and look around the forums again.

You've absolutely hit the nail on the head around dtreeancestors, it was reccomender causing needless load to database. No one even liked its functionality on our estate. I'll discuss this with OT now we're on a newer version as it would make life easier.

Thanks very much for your responses :-)