nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

LiveReport to view documents in folder and sub-folders

Atiya_Sultana

Hi,I have used the below query to get the documents and their versions in a folder.SELECT DTree.DataID, DTree.Name, DVersData.Version, DVersData.FileName, DVersData.MimeType, DVersData.DocID, DTree.ParentIDFROM DVersData (nolock), DTree (nolock) where DVersData.DocID = DTree.DataID AND (DTree.SubType = 144) and DTree.ParentID=100099 ORDER BY DTree.NameBut my requirement is to be able to dipaly documents names along with vesions in the sub-folders as well.Can anyone help?Many Thanks,Atiya.

Find more posts tagged with

Comments

Bhupinder_Singh

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

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

If you are using an Oracle database with Livelink, you will need to introduce a "connect by prior" clause to traverse subfolders. In that case, the LiveReport provided at the link below may be very useful:https://knowledge.opentext.com/go/4466387

Some suggestions for addressing the "Connect by prior" problem with SQL Server can be found here:

https://knowledge.opentext.com/go/13448797

...and here:

https://knowledge.opentext.com/go/4610762

...and here:

https://knowledge.opentext.com/go/11725974

- Bhupinder

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

From: eLink Discussion: Open Text Live Reports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Wednesday, September 16, 2009 11:32 AM
To: eLink Recipient
Subject: LiveReport to view documents in folder and sub-folders

LiveReport to view documents in folder and sub-folders

Posted by atiya.sultana@accenture.com (Sultana, Atiya) on 2009/09/16 11:31

Hi,

I have used the below query to get the documents and their versions in a folder.

SELECT DTree.DataID, DTree.Name, DVersData.Version, DVersData.FileName,
DVersData.MimeType, DVersData.DocID, DTree.ParentID
FROM DVersData (nolock), DTree (nolock) where DVersData.DocID = DTree.DataID AND (DTree.SubType = 144) and DTree.ParentID=100099 ORDER BY DTree.Name

But my requirement is to be able to dipaly documents names along with vesions in the sub-folders as well.

Can anyone help?

Many Thanks,
Atiya.

Atiya_Sultana

Thanks for the reply Bhupinder.But, we use SQL Server 2005. Is this possible through this db?

Bob_Guilford

message body too large, it has been made into an attachment

Atiya_Sultana

Thanks Bob.It's very helpful.But I get the error "Cannot find either column "lldb" or the user-defined function or aggregate "lldb.HOV_return_Object_Path", or the name is ambiguous" when I execute the sql with path.Can anyone help?Many Thanks,Atiya.

Bob_Guilford

Message from Guilford, Bob <BGuilford@hovensa.com> via eLink

eLink

The path procedure has to be added to the LLdatabase on sql server. If you have added it correctly, you should be able todo a simple query such as

SELECT Name,LLProdUser.hov_return_object_Path(DataID) AS Expr1

FROM LLProdUser.DTree

WHERE (DataID =671710)

and get name and the path (see screenshot):

Perhaps there is an issue with getting theprocedure added correctly. Hope this helps.

Bob

From: eLink Discussion: Open Text Live Reports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, September 17, 200911:25 AM
To: eLink Recipient
Subject: Thanks Bob.

Thanks Bob.		Posted by atiya.sultana@accenture.com (Sultana, Atiya) on 2009/09/17 11:20
In reply to: RE Is it possible with SQL Server 2005?		Posted by bguilford (Guilford, Bob) on 2009/09/17 08:48

Thanks Bob.

It's very helpful.

But I get the error "Cannot find either column "lldb" or the user-defined function or aggregate "lldb.HOV_return_Object_Path", or the name is ambiguous" when I execute the sql with path.

Can anyone help?

Many Thanks,
Atiya.

Atiya_Sultana

The procedure got added successfully. This query also gives the same error.Do I have add the path procedure every time before i run the query?Thanks,Atiya.

Bob_Guilford

Message from Guilford, Bob <BGuilford@hovensa.com> via eLink

eLink

The procedure only needs to be added onceto the database, it should show in Mgmt Studio in the LL database. I suggestverifying the procedure is set for your environment (ie right user id..), etc.Originally, I worked on the procedure as sql code so I could tweak it moreeasily. You might try running it as a standalone query with a folder dataid inyour LL, such as:

WITH hierarchy(parentid, dataid, LEVEL, Name,Path) AS

(SELECTParentid, dataid, 0, name, cast(Name ASnvarchar(1024))

FROM llproduser.dtree

WHERE dataID = 1568215

UNION ALL

SELECT dt.parentid, dt.dataid, LEVEL + 1,dt.name, cast(dt.name AS nvarchar(1024))

FROM llproduser.dtree dt INNER JOIN

hierarchy h ON dt.dataid = h.parentid),

hierarchyReverse(parentid, dataid, LEVEL, Name,Path)

(SELECT Parentid, dataid, 0,name, cast(Name ASnvarchar(1024))

FROM Hierarchy

WHERE parentid = - 1

UNION ALL

SELECT h.parentid, h.dataid, hr.LEVEL+ 1, h.name, cast(hr.Path+ ':' +h.Path AS nvarchar(1024))

FROM Hierarchy h INNER JOIN

HierarchyReverse hr ONh.parentid = hr.dataid)

SELECT path

FROM HierarchyReverse

WHERE dataID = 1568215

ORDER BY LEVEL

Then tweak ituntil the issues are resolved, then tweak the procedure with the same fixes.This gives me the result:

path

Recycle Bin:2009:09:16:0930 HP Fuel GasAmine Contactor Unit Specifications, Calibrations and Instrumentation Indexes 3

Bob

From: eLink Discussion: Open Text Live Reports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, September 17, 200912:28 PM
To: eLink Recipient
Subject: The procedure got addedsuccessfully. This query also gives the same error.

The procedure got added successfully. This query also gives the same error.		Posted by atiya.sultana@accenture.com (Sultana, Atiya) on 2009/09/17 12:26
In reply to: RE Thanks Bob.		Posted by bguilford (Guilford, Bob) on 2009/09/17 12:12

The procedure got added successfully. This query also gives the same error.

Do I have add the path procedure every time before i run the query?

Thanks,
Atiya.

Atiya_Sultana

Hi Bob,As mentioned, first I have created the below procedure in the lldb databaseCreate Procedure [lldb].[HOV_Get_Object_Path] @ID INT, @ObjectPath nvarchar(1024) OUTPUTAS WITH hierarchy(parentid, dataid, LEVEL, Name, Path) AS (SELECT Parentid, dataid, 0, name, cast(Name AS nvarchar(1024))FROM lldb.dtree WHERE dataID = @IDUNION ALL SELECT dt.parentid, dt.dataid, LEVEL + 1, dt.name, cast(dt.name AS nvarchar(1024))FROM lldb.dtree dt INNER JOINhierarchy h ON dt.dataid = h.parentid), hierarchyReverse(parentid, dataid, LEVEL, Name, Path)AS (SELECT Parentid, dataid, 0, name, cast(Name AS nvarchar(1024)) FROM Hierarchy WHERE parentid = - 1 UNION ALL SELECT h.parentid, h.dataid, hr.LEVEL + 1, h.name, cast(hr.Path + ':' + h.Path AS nvarchar(1024)) FROM Hierarchy h INNER JOIN HierarchyReverse hr ON h.parentid = hr.dataid)SELECT @ObjectPath=Path FROM HierarchyReverseWHERE dataID = @ID ORDER BY LEVELExecuting the above procedure, gave me "Command completed successfully" in the results pane. After this, I execute the below query which gives the error "Cannot find either column "lldb" or the user-defined function or aggregate "lldb.HOV_return_Object_Path", or the name is ambiguous"the above procedure, WITH hierarchy(parentid, dataid, LEVEL, Name, sort) AS (SELECT Parentid, dataid, 0, [name], cast(Name AS nvarchar(1024)) FROM lldb.dtree WHERE parentid = 710719 UNION ALL SELECT dt.parentid, dt.dataid, LEVEL + 1, dt.name, cast(Sort + '|' + dt.name AS nvarchar(1024)) FROM lldb.dtree dt INNER JOIN hierarchy h ON dt.parentid = h.dataid) SELECT Dataid, parentID, name, LEVEL, lldb.hov_return_object_Path(DataID) AS Path FROM Hierarchy ORDER BY sortUnable to figure out what wrong I am doing here.Many Thanks,Atiya.

Atiya_Sultana

Hi,Could anyone please help me know what am I doing wrong here to get the required results?Many Thanks,Atiya.

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

Atiya,

Which RDBMS are you using?

Rgds,

Adarsh

-----Original Message-----
From: eLink Discussion: Open Text Live Reports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Wednesday, September 23, 2009 4:16 PM
To: eLink Recipient
Subject: Hi,

Hi, Posted by atiya.sultana@accenture.com (Sultana, Atiya) on 2009/09/23 06:42
In reply to: Hi Bob, Posted by atiya.sultana@accenture.com (Sultana, Atiya) on 2009/09/18 05:13

Hi,

Could anyone please help me know what am I doing wrong here to get the required results?

Many Thanks,
Atiya.

Tim_Hunter

Do you have a lldb schema? If not change lldb to your schema or remove the lldb completely and create everything in your livelink schema.

Atiya_Sultana

Hi Adarsh,We use SQL Server 2005.Many Thanks,Atiya.

Atiya_Sultana

Hi Tim,Actually, the schema and the db name are same.Many Thanks,Atiya.

Atiya_Sultana

It seems stored procedure cannot be used in a select statement.Can anyone help me in sorting this out?Many Thanks,Atiya.

Bob_Guilford

Message from Guilford, Bob <BGuilford@hovensa.com> via eLink

eLink

Hi Atiya,

I don’t know what’s going onfor you, they work in select statements on our sql 2005 server.

Bob

From: eLinkDiscussion: Open Text Live Reports Discussion[mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, October 01, 200912:01 PM
To: eLink Recipient
Subject: It seems stored procedurecannot be used in a select statement.

It seems stored procedure cannot be used in a select statement.		Posted by atiya.sultana@accenture.com (Sultana, Atiya) on 2009/10/01 11:57
In reply to: Hi Bob,		Posted by atiya.sultana@accenture.com (Sultana, Atiya) on 2009/09/18 05:13

It seems stored procedure cannot be used in a select statement.

Can anyone help me in sorting this out?

Many Thanks,
Atiya.

Rouven_Bilgeri

Hi, I don't know if it's exactly what you're looking for but maybe it'll bring you forward...XXXXXXXXX --> put in DataID"AND dt.ownerid <> -2320602" excepts the RecBinBR, RouvenWITH recursive_tempDTREE (level, parentid, dataid, name, subtype, laenge) AS((SELECT 1, parentid, dataid, name, subtype, LEN(name) FROM dtree WHERE parentid = XXXXXXXXX)UNION ALL(SELECT level + 1, a.parentid, b.dataid, b.name, b.subtype, laenge + LEN(b.name) FROM recursive_tempDTREE as "a", dtree as "b" WHERE b.parentid = a.dataid OR (b.parentid * -1) = a.dataid))SELECT level, dt.parentid, c.dataid, c.name, c.subtype, c.laenge, dv.datasize/1024 as datasize_kB, dv.versionFROM recursive_tempDTREE as "c", dtree as "dt", dversdata as "dv"WHERE dt.dataid = c.dataid AND dt.ownerid <> -2320602 AND c.dataid = dv.docid AND dt.dataid = dv.docidUNIONSELECT level, dt.parentid, c.dataid, c.name, c.subtype, c.laenge, 0, 0FROM recursive_tempDTREE as "c", dtree as "dt"WHERE dt.dataid = c.dataid AND dt.ownerid <> -2320602 AND c.subtype <> 144GROUP BY c.level, c.parentid, c.dataid, dt.parentid, c.name, c.subtype, c.laenge

Atiya_Sultana

Hi,The below sql query works fine to give me the required result except that when I place 'order by c.PathStr,c.Dataid' at the end it gives the error "ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator."WITH recursive_tempDTREE (level, parentid, dataid, name, Subtype, Pathstr ) AS((SELECT 1, parentid, dataid, Name, Subtype as ObjectType, CAST ('' as VARCHAR(MAX)) as Location FROM dtree (nolock) WHERE parentid = XXXXX)UNION ALL(SELECT level + 1, a.parentid, b.dataid, b.Name, b.subtype, pathstr+ '\' + cast(b.name AS varchar(max)) FROM recursive_tempDTREE as "a", dtree as "b" WHERE b.parentid = a.dataid OR (b.parentid * -1) = a.dataid))SELECT SPACE(level*10)+ c.Name as Name, c.dataid, dt.parentid, level, case c.subtype when 0 then 'Folder' when 144 then 'Document' Else 'Other Object' End As ObjectType, pathstr+ '\' + cast(c.name AS varchar(max)) as Location , dv.datasize/1024 as datasize_kB, dv.versionFROM recursive_tempDTREE as "c", dtree as "dt", dversdata as "dv"WHERE dt.dataid = c.dataid AND c.dataid = dv.docid AND dt.dataid = dv.docidUNIONSELECT SPACE(level*10)+ c.Name as Name, c.dataid, dt.parentid, level, case c.subtype when 0 then 'Folder' when 144 then 'File' Else 'Other Object' End As ObjectType,pathstr as Location, 0,0FROM recursive_tempDTREE as "c", dtree as "dt"WHERE dt.dataid = c.dataid AND (c.subtype = 144 OR c.subtype=0) GROUP BY c.level, c.parentid, c.dataid, dt.parentid, c.name, c.subtype,c.PathStr 1) I need to use order by clause to achive the required result set. When I place order by in other select statement it gives errors. Can anyone please help?2) And in SQL results pane, I could see th spaces (hierarchy) but when I use the same query in Livelink's LiveReport hierarchy is not shown. Is there a way to achieve this in LiveReports?Many Thanks,Atiya.

Atiya_Sultana

Instead of "order by c.Pathstr,c.dataid" we need to give"order by pathstr+ '\' + cast(c.name AS varchar(max)), c.dataid" in the query at the end.Working out on removing the path appearing twice.

Atiya_Sultana

Hi,This is the final query to display documents and folders in a folder along with the path, level, documents size and their versions.WITH recursive_tempDTREE (level, parentid, dataid, name, Subtype, Pathstr ) AS ((SELECT 1, parentid, dataid, Name, Subtype as ObjectType, CAST ('' as VARCHAR(MAX)) FROM dtree (nolock) WHERE parentid = XXXXX) UNION ALL (SELECT level + 1, a.parentid, b.dataid, b.Name, b.subtype, Pathstr+ '\' + cast(a.name AS varchar(max)) as Location FROM recursive_tempDTREE as "a", dtree as "b" (nolock) WHERE b.parentid = a.dataid OR (b.parentid * -1) = a.dataid)) SELECT SPACE(level*10)+ c.Name as Name, c.dataid, dt.parentid, level, case c.subtype when 0 then 'Folder' when 144 then 'Document' Else 'Other Object' End As ObjectType, pathstr + '\' + cast(c.name AS varchar(max))as Location , dv.datasize/1024 as datasize_kB, dv.version, case la.ValStr when 'Status' Then 'Status' end as "Atos Cat Status", case la.ValStr When 'Version' Then 'Version' end as "Col2" FROM recursive_tempDTREE as "c", dtree as "dt" (nolock), dversdata as "dv" (nolock) WHERE dt.dataid = c.dataid AND c.dataid = dv.docid AND dt.dataid = dv.docid UNION SELECT SPACE(level*10)+ c.Name as Name, c.dataid, dt.parentid, level, case c.subtype when 0 then 'Folder' when 144 then 'Document' Else 'Other Object' End As ObjectType,pathstr+ '\' + cast(c.name AS varchar(max)) as Location,0,0 FROM recursive_tempDTREE as "c", dtree as "dt" (nolock) WHERE dt.dataid = c.dataid AND (c.subtype = 144 OR c.subtype=0) GROUP BY c.level, c.parentid, c.dataid, dt.parentid, c.name, c.subtype,c.pathstr order by pathstr+'\'+CAST (c.name as VARCHAR(MAX)), c.dataidThanks Rouven for the reply to the post.Many Thanks,Atiya.

Rouven_Bilgeri

Hello Atiya, I tested your query and it gave me too many results back. Could be, that you want it that way too.At the end you select for " AND (c.subtype = 144 OR c.subtype=0)"My selection was "c.subtype <> 144 " which gives another result. Additional I excluded the id of the recycle bin, that only "active" versions are displayed. But real good query with creating the paths... Have fun, Rouven

Jean-Philippe_Martin

Message from <Martin.Jean-Philippe@hydro.qc.ca> via eLink

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

You can use sys_connect_by_path with Oracle 9i + to have Oracle compute the whole path.

ex:

select name, level, dataid, sys_connect_by_path (name, '/') path
from dtree
CONNECT BY PRIOR parentid = dataid
start with dataid = 9728275

De : eLink Discussion: Open Text Live Reports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Envoyé : jeudi 22 octobre 2009 07:43
À : eLink Recipient
Objet : Final query

Final query Posted by atiya.sultana@accenture.com (Sultana, Atiya) on 2009/10/22 07:40
In reply to: Got it Posted by atiya.sultana@accenture.com (Sultana, Atiya) on 2009/10/12 07:53

Hi,

This is the final query to display documents and folders in a folder along with the path, level, documents size and their versions.

WITH recursive_tempDTREE (level, parentid, dataid, name, Subtype, Pathstr ) AS ((SELECT 1, parentid, dataid, Name, Subtype as ObjectType, CAST ('' as VARCHAR(MAX)) FROM dtree (nolock) WHERE parentid = XXXXX) UNION ALL (SELECT level + 1, a.parentid, b.dataid, b.Name, b.subtype, Pathstr+ '\' + cast(a.name AS varchar(max)) as Location FROM recursive_tempDTREE as "a", dtree as "b" (nolock) WHERE b.parentid = a.dataid OR (b.parentid * -1) = a.dataid)) SELECT SPACE(level*10)+ c.Name as Name, c.dataid, dt.parentid, level, case c.subtype when 0 then 'Folder' when 144 then 'Document' Else 'Other Object' End As ObjectType, pathstr + '\' + cast(c.name AS varchar(max))as Location , dv.datasize/1024 as datasize_kB, dv.version, case la.ValStr when 'Status' Then 'Status' end as "Atos Cat Status", case la.ValStr When 'Version' Then 'Version' end as "Col2" FROM recursive_tempDTREE as "c", dtree as "dt" (nolock), dversdata as "! dv" (nolock) WHERE dt.dataid = c.dataid AND c.dataid = dv.docid AND dt.dataid = dv.docid UNION SELECT SPACE(level*10)+ c.Name as Name, c.dataid, dt.parentid, level, case c.subtype when 0 then 'Folder' when 144 then 'Document' Else 'Other Object' End As ObjectType,pathstr+ '\' + cast(c.name AS varchar(max)) as Location,0,0 FROM recursive_tempDTREE as "c", dtree as "dt" (nolock) WHERE dt.dataid = c.dataid AND (c.subtype = 144 OR c.subtype=0) GROUP BY c.level, c.parentid, c.dataid, dt.parentid, c.name, c.subtype,c.pathstr order by pathstr+'\'+CAST (c.name as VARCHAR(MAX)), c.dataid

Thanks Rouven for the reply to the post.

Many Thanks,
Atiya.

Evert_Kuiken_(ekuiken_-_(deleted))

Message from <Evert.Kuiken@alliander.com> via eLink

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

That's a good one! New to me.

This produces the path, but in a reverse order. Is there a way to rewrite this into a form like

\Enterprise\subfolder\sub-subfolder\...

Evert

Tim_Hunter

It's ugly, but it works.select name, level, dataid, sys_connect_by_path (name, '/') backwardspath, rtrim(utl_raw.cast_to_varchar2(utl_raw.reverse(utl_raw.cast_to_raw(sys_connect_by_path(utl_raw.cast_to_varchar2(utl_raw.reverse(utl_raw.cast_to_raw(name))),'/')))),'/') pathfrom dtreeCONNECT BY PRIOR parentid = dataidstart with dataid = 1234567890

Jean-Philippe_Martin

good and cleaver !! I tried to make your query fit on one line:select pathfrom ( select rtrim(utl_raw.cast_to_varchar2(utl_raw.reverse(utl_raw.cast_to_raw(sys_connect_by_path(utl_raw.cast_to_varchar2(utl_raw.reverse(utl_raw.cast_to_raw(name))),'/')))),'/') path from dtree CONNECT BY PRIOR parentid = dataid start with dataid = 18909268 )where length(path) = ( select max(length(path)) from ( select rtrim(utl_raw.cast_to_varchar2(utl_raw.reverse(utl_raw.cast_to_raw(sys_connect_by_path( utl_raw.cast_to_varchar2(utl_raw.reverse(utl_raw.cast_to_raw(name))),'/')))),'/') path from dtree CONNECT BY PRIOR parentid = dataid start with dataid = 18909268 ) )I have this one which I build from an example on stackoverflow that fit on a single line also (the trick is in the connect by) :select sys_connect_by_path (d3.name, '/') namefrom (select d2.name, d2.dataid, d2.parentid, level lvl from dtree d2 start with d2.dataid = 9728275 connect by prior d2.parentid = d2.dataid ) d3where d3.dataid = 9728275and rownum = 1 connect by prior d3.dataid = d3.parentid