Windchill常用SQL语句

查询1：有多少WTPart没有默认表示法

SELECT COUNT(*)

FROM wtpart

WHERE wtpart.ida2a2 not in (select derivedimage.ida3therepresentablereferenc from derivedimage);

备注：这个查询包括部件中没有关联CAD文档的情况

查询2：有多少CAD文档（和“关联”的部件无关）没有默认表示法

SELECT count(*) FROM DerivedImage

  WHERE CLASSNAMEKEYTHEREPRESENTABLE='wt.epm.EPMDocument'

  AND DerivedImage.IDA3THEREPRESENTABLEREFERENC

  NOT IN (SELECT EPMBuildHistory.IDA3A5 FROM EPMBuildHistory WHERE BUILDTYPE=7);

查询3：有多少CAD文档的装配的表示法不是定位装配类型

SELECT distinct EpmdocumentMaster.Name

  FROM EPMDocument, EPMDocumentMaster, DerivedImage

  WHERE EpmDocument.ida3masterreference = EPMDocumentMaster.ida2a2

  AND EPMDocument.ida2a2=derivedimage.ida3a6

  AND DerivedImage.defaultrepresentation=1

  AND DerivedImage.additionalinfo is null

  AND EpmDocumentMaster.name like '%.asm';

查询4：列出所有CAD文档中带有表示法的条目

SELECT epmdocumentmaster.documentnumber, epmdocumentmaster.name, epmdocumentmaster.cadname, epmdocument.versionida2versioninfo "Rev", epmdocument.iterationida2iterationinfo "Iteration"

  FROM epmdocument, epmdocumentmaster, derivedimage

  WHERE  epmdocument.ida3masterreference = epmdocumentmaster.ida2a2

  AND epmdocument.ida2a2 IN derivedimage.ida3therepresentablereferenc;

查询5：列出所有部件中带有表示法的条目

SELECT wtpartmaster.wtpartnumber, wtpartmaster.name, wtpart.versionida2versioninfo "Rev", wtpart.iterationida2iterationinfo "Iteration"

  FROM wtpart, wtpartmaster, derivedimage

  WHERE  wtpart.ida3masterreference = wtpartmaster.ida2a2

  AND wtpart.ida2a2 IN derivedimage.ida3therepresentablereferenc;

备注：以上所有查询语句使用Oracle SQL*Plus 语法，如果使用SQL Server形式可能需做一定的调整