SQL Query for document Context/Location

[jwelsh]

Does anyone have a SQL query to list of all documents, with their context & location.

I did setup a query in report builder, however I run into a record count limitation (65k) when exporting.  When using the SQL query from this report in SQL itself, it only brings over the IDs for context and folder path (I need the names).  I must need to link to another table, even though the report builder SQL is not showing such?

[BenPerry]

Joe‌,

What you might be experiencing is that Query Builder handles the different types of contexts that your document could reside - Project, Product, Library.  But with raw SQL to bypass the query limit, you'll likely need to create 3 separate SQL queries to search against those 3 different types of contexts.

Do you have a test site?  Perhaps you could set the query limits to -1 on that site, and execute the query.  Then you can find the number of results (say perhaps 95k) and then temporarily set your production query limits accordingly (say 100k).  Does that help?

...or... perhaps what you're talking about is not a "query limit" limit, but instead an export functionality limit.  Perhaps CS52471 describes your situation.  I have also experienced it.  Therefore I used the information in that article to set the Windchill property to 1,048,576 (or a few less than that) - the new row limit in the newer versions of Excel.

[BineshKumar1 · Accepted solution]

I have attached a query which I used for our prod a couple of time. Hope it helps.

Thanks

Binesh Kumar

Barry Wehmiller

[jwelsh]

Thank you Binesh!

Not only did that work out perfectly, I now have some material to help me to begin understanding some of the DB relationships.

[BhushanNehe]

Hi Joe,

If the QML worked for you, can you mark the answer as correct to make it easy for other members in the community.

Regards,

Bhushan

[mbrock]

Hi Binesh,

Looks like this simply returns the base level folder an object resides in. We could potentially have the same folder name multiple times in one context for different products in that line. For example, if we have a product Product with product lines Line1, Line2, and Line3, we could have three "Engineering" folders that would have full filepath as /Default/Line1/Engineering, /Default/Line2/Engineering, and Default/Line3/Engineering. In this case the folder information being returned by your query isn't exceptionally useful. Do you have a way to display the full filepath for the objects? 

Thanks,

Marshall

[aaboobacker]

This might help. It is to list all epm documents in a Product called "PRODUCT1" :

select mas.NAME, epm.VERSIONIDA2VERSIONINFO as VERSION, mas.DOCTYPE, mas.DOCUMENTNUMBER, mas.CADNAME, mas.AUTHORINGAPPLICATION, epm.STATESTATE, 'PRODUCT1' as Container , foldr.NAME as Folder, foldr.ida2a2,
(select sys_connect_by_path(Name, '/')
from SUBFOLDER where ida2a2=foldr.ida2a2
start with IDA3B2FOLDERINGINFO = 0
connect by prior ida2a2 = IDA3B2FOLDERINGINFO) as Location
from EPMDOCUMENTMASTER mas, epmdocument epm, SUBFOLDER foldr
where mas.IDA3CONTAINERREFERENCE=(select contr.ida2a2 from pdmlinkproduct contr where contr.NAMECONTAINERINFO in ('PRODUCT1')) and
mas.CLASSNAMEKEYCONTAINERREFEREN='wt.pdmlink.PDMLinkProduct' and
mas.ida2a2 = epm.IDA3MASTERREFERENCE and
epm.LATESTITERATIONINFO=1 and
foldr.ida2a2 = epm.IDA3B2FOLDERINGINFO;

The Location is retrieved as a Folder Path using a hierarchical query. Hope it helps!