Need help showing parent folder names..

David_Beliveau_(sanmin013_-_(deleted))

I am using the following sql (ms sql 7.0) in an autolivereport to show what documents and individual has at least "see" permissions to. Select RightId, dtree.parentid, dtree.Name from dtreeacl, dtree where dtreeacl.dataid=dtree.dataid and rightid=%1 and permissions<>128 and %2 order by NameThe output includes the document name and the parent folder ID. I would like to be able to show the folder Name rather than ID. I would also like very much to be able to limit the search to a specific heirarchy of folders/subfolders. Does anyone know how to accomplish this? Any help would be greatly appreciated.Thanks,Dave Beliveau

eLink User

Message from Marie Lindsay <<A HREF="mailto:marie@opentext.com">marie@opentext.com> via eLink

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

How about something like this instead:

 

select parent.name "Parent", dtree.name "Name", 
 ( case dtreeacl.see
   when 0 then 'On ACL but no permissions'
   when 1 then 'See'
   when 2 then 'See Contents'
   when 3 then 'Modify'
   when 4 then 'Delete'
   else 'Not specified' end ) "Permission"
  from dtree, dtree parent, dtreeacl
  where dtreeacl.rightid = %1
 and dtree.dataid = dtreeacl.dataid
 and dtree.parentid = parent.dataid

and %2
  order by dtreeacl.see

 

Where %1 identifies the user and %2 is "filter documents".

 

This example doesn't limit to a particular branch of the hierarchy... You'd have to use a CONNECT BY statement in Oracle or a stored procedure in MSSQL to accomplish that task. See other topics in this discussion for more info about hierarchical queries (or come to the Advanced Schema and LiveReports course). Hope this helps!

-----Original Message-----
From: eLink Discussion: Livelink LiveReports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Wednesday, August 28, 2002 9:43 AM
To: eLink Recipient
Subject: Need help showing parent folder names..

 

Need help showing parent folder names..

Posted by SANMIN013 (Beliveau, David) on 08/28/2002 10:34 AM

I am using the following sql (ms sql 7.0) in an autolivereport to show what documents and individual has at least "see" permissions to. Select RightId, dtree.parentid, dtree.Name from dtreeacl, dtree where dtreeacl.dataid=dtree.dataid and rightid=%1 and permissions<>128 and %2 order by Name The output includes the document name and the parent folder ID. I would like to be able to show the folder Name rather than ID. I would also like very much to be able to limit the search to a specific heirarchy of folders/subfolders. Does anyone know how to accomplish this? Any help would be greatly appreciated. Thanks, Dave Beliveau