LiveReport using ORACLE SQL

Using Oracle SQL what is the LiveReport to list in hierarchical format the folder names and owner of the folders?

Find more posts tagged with

Comments

Message from Bhupinder Singh <bsingh@opentext.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

You might be able to use the reports in the thread referenced below as a starting point for your own report:

https://knowledge.opentext.com/knowledge/llisapi.dll?func=ll&objId=2961591&objAction=view&show=2

- Bhupinder----------------------------------------------
Bhupinder Singh, B.Math, B.Ed.
Senior Systems Analyst, Information Technology
Open Text, Waterloo, Ontario, Canada
----------------------------------------------

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, May 21, 2009 4:15 PM
To: eLink Recipient
Subject: LiveReport using ORACLE SQL

LiveReport using ORACLE SQL

Posted by unilev02user2 (Song, Ann) on 2009/05/21 16:13

Using Oracle SQL what is the LiveReport to list in hierarchical format the folder names and owner of the folders?

Ann_Song

Thank you Bhupinder...I checked out the previous threads and came up with this:select lpad(' ', level * 5, '-') || name "Name", subtype "Subtype", parentid "ParentID", dataid "DataID", groupid "GroupID", createdby "CreatedBy" from dtree where subtype = 0 start with dataid = %1 connect by parentid = PRIOR dataidIt works great but I need to include the "owner" (not creator) of each folder and can't seem to get it to work when including kuaf in sql.Any help would be appreciated....thanks

Appu_Nair

I could be wrong but dtree records objects subtype and created by.Dtreeacl has the info that you look for based on dtree dataid.You will find UGO and other ACL's from that table.To keep it simple find a simple folder dataid and look for that info in dtreeacl.To convert the id to a friendly name you will need to tie back on kuaf.

Lindsay_Davies

Message from Lindsay Davies <ldavies@opentext.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

Hi Ann,

The current owner is stored in DTree.UserID and this can be converted from a number to a login name or first+last name if you join that value to Kuaf.ID

If you were trying the join with OwnerID that would not work, because OwnerID represents the workspace that contains (owns) the items, not a user.

Apologies for the confusing use of terminology, but it has been around since Livelink began.

Regards

Lindsay Davies

European Escalation Team

Opentext

From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 22 May 2009 15:21
To: eLink Recipient
Subject: Add "owner" to Oracle hierarchy sql

Add "owner" to Oracle hierarchy sql		Posted by unilev02user2 (Song, Ann) on 2009/05/22 10:19
In reply to: LiveReport using ORACLE SQL		Posted by unilev02user2 (Song, Ann) on 2009/05/21 16:13

Thank you Bhupinder...I checked out the previous threads and came up with this:

select lpad(' ', level * 5, '-') || name "Name", subtype "Subtype", parentid "ParentID", dataid "DataID", groupid "GroupID", createdby "CreatedBy" from dtree where subtype = 0 start with dataid = %1 connect by parentid = PRIOR dataid

It works great but I need to include the "owner" (not creator) of each folder and can't seem to get it to work when including kuaf in sql.

Any help would be appreciated....thanks

adarsh_lobo_(adarsh.lobo@shell.com_(Delete)_112626

Message from <Adarsh.Lobo@shell.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

Ann,

Due you mean to say that you are not able to join dtree.userid with kuaf.id in the connect by prior clause. If that is the case try the following query:

select lpad(' ', level * 5, '-') || name "Name", subtype "Subtype", parentid "ParentID", dataid "DataID", groupid "GroupID", createdby "CreatedBy", (select name from kuaf where id=userid) "Owner" from dtree where subtype = 0 start with dataid = %1 connect by parentid = PRIOR dataid

Similarly you can also get the group name and name of the user who created the folder.

Thanks,

Adarsh

-----Original Message-----
From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Friday, May 22, 2009 8:22 PM
To: eLink Recipient
Subject: RE Add "owner" to Oracle hierarchy sql

RE Add "owner" to Oracle hierarchy sql Posted by ldavies (Davies, Lindsay) on 2009/05/22 10:51
In reply to: Add "owner" to Oracle hierarchy sql Posted by unilev02user2 (Song, Ann) on 2009/05/22 10:19

Message from Lindsay Davies <ldavies@opentext.com> via eLink

Hi Ann, The current owner is stored in DTree.UserID and this can be converted from a number to a login name or first+last name if you join that value to Kuaf.ID If you were trying the join with OwnerID that would not work, because OwnerID represents the workspace that contains (owns) the items, not a user. Apologies for the confusing use of terminology, but it has been around since Livelink began. Regards Lindsay Davies European Escalation Team Opentext
From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 22 May 2009 15:21
To: eLink Recipient
Subject: Add "owner" to Oracle hierarchy sql

Add "owner" to Oracle hierarchy sql Posted by unilev02user2 (Song, Ann) on 2009/05/22 10:19
In reply to: LiveReport using ORACLE SQL Posted by unilev02user2 (Song, Ann) on 2009/05/21 16:13

Thank you Bhupinder...I checked out the previous threads and came up with this:

select lpad(' ', level * 5, '-') || name "Name", subtype "Subtype", parentid "ParentID", dataid "DataID", groupid "GroupID", createdby "CreatedBy" from dtree where subtype = 0 start with dataid = %1 connect by parentid = PRIOR dataid

It works great but I need to include the "owner" (not creator) of each folder and can't seem to get it to work when including kuaf in sql.

Any help would be appreciated....thanks

Ann_Song

Adarsh/Lyndsay,Yes, that's exactly the script I was looking for....works like a charm. I'm a little rusty with Oracle SQL.Thanks very much for your help.Ann