Adding Path in a LiveReport

Hi,

I already prepared this SQL:

SELECT DTREE.NAME, DTREE.SUBTYPE, DTREEANCESTORS.ANCESTORID, DTREE.DATAID, LLATTRDATA.DEFID, LLATTRDATA.ATTRID, LLATTRDATA.VALSTR FROM DTREE, DTREEANCESTORS, LLATTRDATA WHERE LLATTRDATA.ID = DTREE.DATAID AND DTREEANCESTORS.DATAID = DTREE.DATAID AND ((LLATTRDATA.DEFID= CategoryID) AND (DTREEANCESTORS.ANCESTORID= FolderID) AND (LLATTRDATA.ATTRID>1))

I would like to add the path of each subfolders and files returned in my results so I will be able to see the structure. Anybody can tell me how to do?

Thanks!

Hugues St-Louis, MTQ

Find more posts tagged with

Comments

komments

I am by no means a SQL expert but I would think something like the following would work:

SELECT DTREE.NAME, DTREE.SUBTYPE, DTREEANCESTORS.ANCESTORID,

“http://path/otcs (or livelink)/llisapi.dll?func=doc.Fetch&nodeid=”+DTREE.DATAID,

LLATTRDATA.DEFID, LLATTRDATA.ATTRID, LLATTRDATA.VALSTR

FROM DTREE, DTREEANCESTORS, LLATTRDATA

WHERE LLATTRDATA.ID = DTREE.DATAID AND

DTREEANCESTORS.DATAID = DTREE.DATAID AND

((LLATTRDATA.DEFID= CategoryID) AND

(DTREEANCESTORS.ANCESTORID=FolderID) AND

(LLATTRDATA.ATTRID>1))

--
Greg Kellogg
Phone:              (361) 758-3762
Mobile:             (361) 815-8950
Email:               gkellogg@OpenText.com

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Wednesday, August 08, 2012 4:02 PM
To: eLink Recipient
Subject: Adding Path in a LiveReport

Adding Path in a LiveReport

Posted byhugues.st-louis@mtq.gouv.qc.ca (St-Louis, Hugues) On 08-08-2012 17:01

Hi,

I already prepared this SQL:

SELECT DTREE.NAME, DTREE.SUBTYPE, DTREEANCESTORS.ANCESTORID, DTREE.DATAID, LLATTRDATA.DEFID, LLATTRDATA.ATTRID, LLATTRDATA.VALSTR FROM DTREE, DTREEANCESTORS, LLATTRDATA WHERE LLATTRDATA.ID = DTREE.DATAID AND DTREEANCESTORS.DATAID = DTREE.DATAID AND ((LLATTRDATA.DEFID= CategoryID) AND (DTREEANCESTORS.ANCESTORID=FolderID) AND (LLATTRDATA.ATTRID>1))

I would like to add the path of each subfolders and files returned in my results so I will be able to see the structure. Anybody can tell me how to do?

Thanks!

Hugues St-Louis, MTQ

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Hugues_St-Louis

It doesn't work.

I forget to mention that I am using Livelink 9.5.

Lindsay_Davies

Hi Hugues,

Are you using Oracle or SQL Server as a database?

If SQL Server, which version?

With Oracle you would use a connect by prior construct.

With SQL Server 2005 and above you can use a recursive common table expression.

If a lower release, you would need to use a stored procedure to generate the path – and to do that you would need access to the DB outside of Livelink/Content Server (or get the DBA to do it).

Regards

Lindsay

OpenText UK

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2012 August 09, Thu 18:19
To: eLink Recipient
Subject: RE Adding Path in a LiveReport

RE Adding Path in a LiveReport

Posted byhugues.st-louis@mtq.gouv.qc.ca (St-Louis, Hugues) On 08-09-2012 13:17

It doesn't work.

I forget to mention that I am using Livelink 9.5.

[To post a comment, use the normal reply function]
Topic:	Adding Path in a LiveReport
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Hugues_St-Louis

Hi,

I work with Oracle.

Lindsay_Davies

Hi Hugues

If you are on Oracle 9i and above (I think) you can use the function/pseudocolumn “sys_connect_by_path” when you use a connect by prior to walk a directory tree.

You have to tell it what column to concatenate and what character to use as a separator. We recommend a colon “:” because that character is not allowed to be entered in a name and is used within the product for this purpose.

For example

select name, sys_connect_by_path(name,':') path from dtree where subtype=0 connect by prior dataid=parentid start with dataid=2000 order by path

Will return something like this…

DavSpace :Enterprise:DavSpace

Folder Find :Enterprise:Folder Find

Folder Find 2 :Enterprise:Folder Find 2

Forums :Enterprise:Forums

France :Enterprise:France

1 New Folder :Enterprise:France:1 New Folder

In Folder :Enterprise:In Folder

Sub Folder :Enterprise:In Folder:Sub Folder

New Folder :Enterprise:In Folder:Sub Folder:New Folder

Subby Folder :Enterprise:In Folder:Sub Folder:Subby Folder

New Folder5 :Enterprise:In Folder:Sub Folder:Subby Folder:New Folder5

New Folder6 :Enterprise:In Folder:Sub Folder:Subby Folder:New Folder5:New Folder6

LPAD-7184 :Enterprise:LPAD-7184

Livereports :Enterprise:Livereports

My Saved Searches :Enterprise:My Saved Searches

Complex searches :Enterprise:My Saved Searches:Complex searches

New Folder :Enterprise:New Folder

Regards

Lindsay

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2012 August 10, Fri 14:30
To: eLink Recipient
Subject: RE RE Adding Path in a LiveReport

RE RE Adding Path in a LiveReport

Posted byhugues.st-louis@mtq.gouv.qc.ca (St-Louis, Hugues) On 08-10-2012 09:27

Hi,

I work with Oracle.

[To post a comment, use the normal reply function]
Topic:	Adding Path in a LiveReport
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

Lindsay_Davies

Hi Hugues,

You cannot do that when navigating via DTreeAncestors as there is no hierarchy information present – you will simply get an unstructured list of all the ancestor or child DataIDs according to how you use that table.

If you are using Oracle you would need to use the “connect by prior” construct.

If using SQL server and you are on SQL 2005 or above you can use a recursive Common Table Expression (CTE).

Search through this forum and you will find some examples.

Regards

Lindsay

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2012 August 08, Wed 22:02
To: eLink Recipient
Subject: Adding Path in a LiveReport

Adding Path in a LiveReport

Posted byhugues.st-louis@mtq.gouv.qc.ca (St-Louis, Hugues) On 08-08-2012 17:01

Hi,

I already prepared this SQL:

SELECT DTREE.NAME, DTREE.SUBTYPE, DTREEANCESTORS.ANCESTORID, DTREE.DATAID, LLATTRDATA.DEFID, LLATTRDATA.ATTRID, LLATTRDATA.VALSTR FROM DTREE, DTREEANCESTORS, LLATTRDATA WHERE LLATTRDATA.ID = DTREE.DATAID AND DTREEANCESTORS.DATAID = DTREE.DATAID AND ((LLATTRDATA.DEFID= CategoryID) AND (DTREEANCESTORS.ANCESTORID=FolderID) AND (LLATTRDATA.ATTRID>1))

I would like to add the path of each subfolders and files returned in my results so I will be able to see the structure. Anybody can tell me how to do?

Thanks!

Hugues St-Louis, MTQ

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center