Oracle / Windchill File count

[metherington]

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

[BenPerry]

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.

[metherington]

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

[RandyJones]

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

[metherington]

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!

[RandyJones · Accepted solution]

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

[metherington]

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;

[BenPerry]

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.