Skip to main content
M
metherington1-Visitor
1-Visitor
June 16, 2015
Solved

Oracle / Windchill File count

  • June 16, 2015
  • 3 replies
  • 2956 views

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

Best answer by RandyJones

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

3 replies

BenPerry
BenPerry15-Moonstone
15-Moonstone
June 16, 2015

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.

    M
    metherington1-VisitorAuthor
    1-Visitor
    June 16, 2015

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

    R
    RandyJones20-Turquoise
    20-Turquoise
    June 16, 2015

    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

      M
      metherington1-VisitorAuthor
      1-Visitor
      June 16, 2015

      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!

      R
      RandyJones20-TurquoiseAnswer
      20-Turquoise
      June 16, 2015

      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

      BenPerry
      BenPerry15-Moonstone
      15-Moonstone
      June 16, 2015

      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.

        Terms & ConditionsAccessibility statement