DB Query that will skip another ParentID

Mark Lumantao

Hello All,

I'm trying to develop a livereport/webreport with a requirement to pull out data from a lot of folders.

Not sure what would be my source folder to use and query to use.

Example:

The Archive Folder is my source and I want to pull out all the data inside the folders inside the Archive Folder.

I checked out the DTree and DTreeCore tables and even if I use the ParentID it's only the folder of the object itself.

Let me know if you require additional information.

Find more posts tagged with

Comments

Appu Nair

Keeping Archive as the main Object if you want all objects like what the OT table is showing one can use other available schema

select * from webnodes where Parentid=2000;

if you want it to show things inside subfolders

if you want to say start at a parent and find everything under that including folders and subfolders starting from 77975

select dt.Name,dt.Dataid,dt.parentid,dt.subtype

from DTree dt,DTreeAncestors dta where dt.DataID=dta.AncestorID and dt.ParentID=779775 order by ParentID

other schema objects DBrowseancestors etc

the schema is free to consult if you sign an NDA with OT

Mark Lumantao

Thank @Appu Nair,

I tried your query. However, it only returned the number if items inside the subfolders.

select dt.Name,dt.Dataid,dt.parentid,dt.subtype from DTree dt,DTreeAncestors dta where dt.DataID=dta.AncestorID and dt.ParentID=779775 order by ParentID

Are you able to share a query that will show the items inside a subfolder.

Example:

You have a parent folder and you want to show all the items of the subfolder of your parent folder, not necessary including the subfolder name just the data inside those subfolders.

Mark Lumantao

@Appu Nair , I can't find a common ID that I can use in DTree.

Appu Nair

So if Archive contains 17999 Objects does the Query not give you 17999 rows? what CS?

Mark Lumantao

Yes it give me the number of object as expected. However, I want to see the names of the objects as well.

Hans Stoop

I normally use

SELECT DataID FROM DTreeAncestors WHERE AncestorID = 1234

This will return all the DataIDs under the item with DataID 1234.

You can get more info about the items by joining DTree.

Appu Nair

ok so my query i s repeating the same information in Name column I will see if I can fix it

Mark Lumantao

This is the results. @Appu Nair

Would like to ask if it's possible that the names inside the subfolders will show instead of the it repeating.

Mark Lumantao

@Hans_Stoop , yes but I can't see a common ID to do my query.

Requirement:

Archive is the source folder and I want to see the items inside subfolders.

Hans Stoop

What about

SELECT AncestorID, DataID FROM DTreeAncestors WHERE AncestorID in (select DataID FROM DTree WHERE ParentID = 1234)

Appu Nair

Same thing as Hans

select Name,SubType,DataId,ParentID from DTree where DataID in ( select DataID from DTreeAncestors where AncestorID=1007694 ) order by ParentID

now you can use WR and print the nodepath and it will even look like the livelink GUI

You also can create a psuedo column called level in SQL to give you indentation.

Mark Lumantao

Thank you @Appu Nair and @Hans_Stoop , both SQLs works.