Folder Path in my query

Hi:

I have written this query to get all the folder structure under a parent folder and now i want to include the folder path.

Any help how this could be acheived?

Select DATAID, OWNERID,NAME, CREATEDATE, ACT from Dtree where dataid in (select dataid from DTreeAncestors where DTreeAncestors.AncestorID= %1 and subtype = 0)

Find more posts tagged with

Comments

Mukesh_Giri

Hi Never mind, i was able to re -write my query, though it took some time to do so sinc ei had a sql based db.

Thanks and if someone in actual come such requirement please feel free to write me.

Quoted mukesh.giri@sce.com on 10/20/2011 03:35 PM:

Hi:

I have written this query to get all the folder structure under a parent folder and now i want to include the folder path.

Any help how this could be acheived?

Select DATAID, OWNERID,NAME, CREATEDATE, ACT from Dtree where dataid in (select dataid from DTreeAncestors where DTreeAncestors.AncestorID= %1 and subtype = 0)

Sunitha_Iyer

Hi,

can you please help me to get Oracle query to retrive the folder path? I use below query but that doesnt work always :

select dataid, name, sys_connect_by_path(name,':') as "Path" , subtype from DTree where dataid in(select dataid from DTree connect by prior dataid=parentid start with dataid=232782) connect by prior dataid=parentid start with dataid=2000

This works fine even when I have more than 15 sub folders and I give leaf node ID. But for the same 15 level hierarchy, If I give 1st level folder and try to retrive the folder path of that It is not working, oracle go on processing but doesnt retrun any result I don't understand that for 15 level it gaave me entire path, but for 1st leavel folder which is very short one it iw not working.

Please find my post at below URL as well for the same:

https://knowledge.opentext.com/knowledge/cs.dll?func=ll&objId=23094279&objAction=viewincontainer&ViewTopicsByReply&ListView&ListViewID=23094279

Narasimha_Rao

Hi Sunitha,

Your query is 100% working without any issues. I have tested with CS10 Oracle 11g environment.

SELECT DataID, Name, sys_connect_by_path(Name,':') as "Path" , SubType
FROM DTree
WHERE DataID IN(SELECT DataID FROM DTree connect by prior DataID=ParentID start with DataID=%1) connect by prior DataID=ParentID start with ParentID=-1

I have tested with lengthy names for folders and files as well. Still your query is fetching results correctly.

If you want specific folder level path then use below query with Level <=3

SELECT DataID, Name, sys_connect_by_path(Name,':') as "Path" , SubType
FROM DTree
WHERE Level <= 3 AND DataID IN(SELECT DataID FROM DTree connect by prior DataID=ParentID start with DataID=%1) connect by prior DataID=ParentID start with ParentID=-1

Finally getting Folder or File path is very easy using WebReports. If you have that option, then using NodeID you can get full path.

[LL_REPTAG_1 Nodeinfo:path /]

Thanks and Regards,
Narsi
Supai Systems,
Bangalore, India.
e-mail : narsi@supaisystems.com
website: www.supaisystems.com

On Tue, Feb 28, 2012 at 5:48 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Folder Path in my query Posted bysunitha_iyer@infosys.com (Iyer, Sunitha Ramesh)On 02-28-2012 07:03

Hi, can you please help me to get Oracle query to retrive the folder path? I use below query but that doesnt work always : select dataid, name, sys_connect_by_path(name,':') as "Path" , subtype from DTree where dataid in(select dataid from DTree connect by prior dataid=parentid start with dataid=232782) connect by prior dataid=parentid start with dataid=2000

This works fine even when I have more than 15 sub folders and I give leaf node ID. But for the same 15 level hierarchy, If I give 1st level folder and try to retrive the folder path of that It is not w! orking, oracle go on processing but doesnt retrun any result I don't understand that for 15 level it gaave me entire path, but for 1st leavel folder which is very short one it iw not working.

Please find my post at below URL as well for the same:

https://knowledge.opentext.com/knowledge/cs.dll?func=ll&objId=23094279&objAction=viewincontainer&ViewTopicsByReply&ListView&ListViewID=23094279

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

Narasimha_Rao

On Tue, Feb 28, 2012 at 5:48 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

Folder Path in my query Posted bysunitha_iyer@infosys.com (Iyer, Sunitha Ramesh)On 02-28-2012 07:03

Hi, can you please help me to get Oracle query to retrive the folder path? I use below query but that doesnt work always : select dataid, name, sys_connect_by_path(name,':') as "Path" , subtype from DTree where dataid in(select dataid from DTree connect by prior dataid=parentid start with dataid=232782) connect by prior dataid=parentid start with dataid=2000

This works fine even when I have more than 15 sub folders and I give leaf node ID. But for the same 15 level hierarchy, If I give 1st level folder and try to retrive the folder path of that It is not w! orking, oracle go on processing but doesnt retrun any result I don't understand that for 15 level it gaave me entire path, but for 1st leavel folder which is very short one it iw not working.

Please find my post at below URL as well for the same:

https://knowledge.opentext.com/knowledge/cs.dll?func=ll&objId=23094279&objAction=viewincontainer&ViewTopicsByReply&ListView&ListViewID=23094279

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

Aditi_Gupta

Hello Mukesh, We are using SQL database for our application and wewant to retrieve the folder path of the documents. Can you please help. Thanks

Mukesh_Giri

Hi Gupta:

Google about the CTE stuffs to understandthe query and try this query , you need to selectthe folder for which you need the path details ()user input.

It should give you results in this format

Folder Name	ObjID	Level	Folder Path
DragDrop4M cs Side	547675	0	DragDrop4M cs Side

WITH tree (data, ID, Level, Pathstr) AS (SELECT NAME, DataID, 0, CAST('' + Name AS nVARCHAR(MAX)) FROM dtree WHERE ParentID =%1 and (SubType = '0' or SubType = '202') UNION ALL SELECT NAME, DataID, t.level + 1, t.pathstr + ' : ' + D.NAME FROM dtree D INNER JOIN tree t ON t.id = D.ParentID and (SubType = '0' or SubType = '202') )SELECT data as "Folder Name", id as ObjID, Level, pathstr as "Folder Path" FROM tree t ORDER BY level

Let me know if it helps,

Cheers,

Mukesh Giri

Quoted adgupta7@in.ibm.com on 2012-03-27 16:02:

Hello Mukesh, We are using SQL database for our application and wewant to retrieve the folder path of the documents. Can you please help. Thanks

Arpita_Mallick

I have write below qury to fetch the folder path of the item.

SELECT DataID, Name, sys_connect_by_path(Name,':') as "Path"FROM DTree
start with DataID=%1 connect by prior DataID=ParentID

This query gives me the output till document name. I want to exclude that part for the docs.

And I also want to see the output like:

Dataid Name Folder1 Folder2 Folder3

12345 ABC Parent1 Parent2 Parent3

Please help to write the query.