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

Oracle / Windchill File count

SOLVED
Highlighted

Oracle / Windchill File count

Afternoon All,

I'm trying to get a total count of .DRW & .PRT & .ASM files on my system.

We are currently using Windchill 10.1 with Oracle.

My current method is by running these Oracle queries:

SQL> SELECT count(*) FROM EPMDocumentMaster WHERE name like '%.asm';

   COUNT(*)

----------

       1568

SQL> SELECT count(*) FROM EPMDocumentMaster WHERE name like '%.prt';

   COUNT(*)

----------

       7055

SQL> SELECT count(*) FROM EPMDocumentMaster WHERE name like '%.drw';

   COUNT(*)

----------

        200

QL> SELECT COUNT(*) FROM EPMDocumentMaster WHERE authoringapplication='PROE';

   COUNT(*)

----------

      19487

However I do not think these values are correct??

In Windchill front end If I search for *.DRW I get over 2k results, but it wont display more than 2k so I cant use this as a reliable counter.

How can I double check these results / Is there a better way to be doing this?

Mike

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: Oracle / Windchill File count

Ahh. You need to query the cadname field instead of name field in epmdocumentmaster ie:

select count(*) from

     epmdocumentmaster edm,

     epmdocument ed

where

     edm.cadname like '%.asm' and

     edm.ida2a2 = ed.ida3masterreference

View solution in original post

7 REPLIES 7
Highlighted

Re: Oracle / Windchill File count

In the bottom left corner of the search results table, do you have a hyperlink that says "Next"?  If yes, click to get the next 2000 results.  You'll have to click it around 9 or 10 times perhaps, to get your complete result.

Else perhaps you can try to export the results by selecting Actions > Export List to File > XLSX.  Shouldn't hurt your system too bad if you think you're only going to get ~20k results.

Highlighted

Re: Oracle / Windchill File count

Thanks Ben this does the trick, little long winded but definitely works!!!

Highlighted

Re: Oracle / Windchill File count

You need to include the epmdocument table in your query. The epmdocumentmaster contains only one row per object. The epmdocument table contains one row per each version of an object. Your query should look like this:

select count(*) from

     epmdocumentmaster edm,

     epmdocument ed

where

     edm.name like '%.asm' and

     edm.ida2a2 = ed.ida3masterreference

Highlighted

Re: Oracle / Windchill File count

When using these joint tables I still only get a few more results -

SQL> select count(*) from
  2  epmdocumentmaster edm,
  3  epmdocument ed
  4  where
  5  upper(edm.name) like '%.PRT' and
  6  upper(edm.ida2a2) = ED.IDA3MASTERREFERENCE;

  COUNT(*)
----------
     10284

SQL> select count(*) from
  2  epmdocumentmaster edm,
  3  epmdocument ed
  4  where
  5  upper(edm.name) like '%.DRW' and
  6  upper(edm.ida2a2) = ED.IDA3MASTERREFERENCE;

  COUNT(*)
----------
       233

SQL> select count(*) from
  2  epmdocumentmaster edm,
  3  epmdocument ed
  4  where
  5  upper(edm.name) like '%.ASM' and
  6  upper(edm.ida2a2) = ED.IDA3MASTERREFERENCE;

  COUNT(*)
----------
      2787

When using Ben's method of pressing the next button I get the following results

(I personally think these results look about the right values)

.drw = 3026

.prt = 4780

.asm = 3695

So which are the accurate results? Why are these different?

Thanks for the quick replys!

Highlighted

Re: Oracle / Windchill File count

Ahh. You need to query the cadname field instead of name field in epmdocumentmaster ie:

select count(*) from

     epmdocumentmaster edm,

     epmdocument ed

where

     edm.cadname like '%.asm' and

     edm.ida2a2 = ed.ida3masterreference

View solution in original post

Highlighted

Re: Oracle / Windchill File count

Perfect Randy thank you very much for that!

This is exactly what I was looking for!

Just to confirm to anyone reading this back in the future these are the commands I ran

select count(*) from
epmdocumentmaster edm,
epmdocument ed
where
upper(edm.cadname) like '%.ASM' and
upper(edm.ida2a2) = ED.IDA3MASTERREFERENCE;

select count(*) from
epmdocumentmaster edm,
epmdocument ed
where
upper(edm.cadname) like '%.DRW' and
upper(edm.ida2a2) = ED.IDA3MASTERREFERENCE;


select count(*) from
epmdocumentmaster edm,
epmdocument ed
where
upper(edm.cadname) like '%.PRT' and
upper(edm.ida2a2) = ED.IDA3MASTERREFERENCE;

Highlighted

Re: Oracle / Windchill File count

FYI...Randy is also right.  You need to define what you mean by "total count of files".  Using only EPMDOCUMENTMASTER table will get you unique part numbers.  The same number of results will be retrieved by doing a Windchill search.

But if you're looking for all revisions and iterations, you need to include the EPMDOCUMENT table and join it like Randy has suggested.

Announcements