cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

SQL Query for document Context/Location

jwelsh
1-Newbie

SQL Query for document Context/Location

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?

1 ACCEPTED SOLUTION

Accepted Solutions

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

Thanks

Binesh Kumar

Barry Wehmiller

View solution in original post

6 REPLIES 6
BenPerry
12-Amethyst
(To:jwelsh)

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.

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

Thanks

Binesh Kumar

Barry Wehmiller

View solution in original post

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
14-Alexandrite
(To:jwelsh)

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
5-Regular Member
(To:BineshKumar1)

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
5-Regular Member
(To:mbrock)

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!

Announcements