including folder path in my Livereport

I have created a live report and want to include folder path of the document. My query is as below.SELECT docid, filename FROM dversdata WHERE docid IN (SELECT dataid FROM dtree START WITH parentid=%1 CONNECT BY PRIOR dataid=parentid) and (length(filename)>%2)

Find more posts tagged with

Comments

Lindsay_Davies

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

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

If you are using Oracle 9i or higher then this is available... using the pseudo column sys_connect_by_path as part of the connect by prior part of your statement.

See this link for a generic example.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions164.htm

So, you might use it as follows

select dataid, name, sys_connect_by_path(name,':') as "Path" , subtype from DTree connect by prior dataid=parentid start with dataid=%1

NOTE: The colon ":" character is used as a folder separator within Livelink - this is not allowed in an object name, so is a reliable choice as the delimiter.
If you wanted to use a slash or backslash, you may find the statement fails because Oracle finds the same character in the name of an object.

So, you have written you current statement to select only dataid from DTree to generate the in-list - this is a sub-select.

You will need to change the structure of your statement combining the building blocks in a different way.

A simple join is not going to work because I believe Oracle does not allow a connect by prior to be part of a table join.

One way round this is to have that select of columns from DTree with its connect by prior used in the form of a pre-select.

select b.dataid, b.path, dv.filename

from dversdata dv,
(select dataid, name, versionnum, sys_connect_by_path(name,':') as "PATH" from dtree
connect by prior dataid=parentid start with dataid=%1 ) b
where dv.docid=b.dataid and dv.version = b.versionnum

So here I am are using the record set from DTree as a new table with an alias name of 'b'.
This avoids having the 'connect by' being spoilt be the join condition.

Regards

Lindsay

Open Text

UK Support

From: eLink Discussion: Open Text Live Reports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2010 May 10, Mon 10:43
To: eLink Recipient
Subject: including folder path in my Livereport

including folder path in my Livereport

Posted by dolene01user3 (user3, dolene01) on 2010/05/10 05:38

I have created a live report and want to include folder path of the document. My query is as below.

SELECT docid, filename FROM dversdata WHERE docid IN (SELECT dataid FROM dtree START WITH parentid=%1 CONNECT BY PRIOR dataid=parentid) and (length(filename)>%2)

Regine_Stiller

I am having the same requirement to display the folder path for a document in livelink. But this time, our potential customer is using MS SQL Server. Any idea how we can solve this problem here? WebReports is not an option for the customer, so we need to find a solution for livereports.Any ideas are highly appreciated!

Appu_Nair

If you search this forum there are examples of SQL server psuedo connect by using a function.Further if it is a newer livelink meaning the patches are all upto date the dtreeancestors table holds this relation as well.The xml outut of a livelink search query contains the pathing information as well if search is a option.To have hyperlinks I beleive if it is a dtree column you have it already out of the box.If you run into a bind having no help I may have a function that I purposed for something like this which I can share although it has been many yrs after that so I have forgotten how it works.