SQL help needed - All released by ORG

[ekinzie]

Good morning PTC Users.

I am in need of some assistance with a query I am trying to write in Oracle SQL Developer. This query will be used to create a CVS file and can't be done in Report Manager.

I am trying to return all WTDocuments that have been RELEASED in the last X number days for a specific Org. What I have seems to work (somewhat) but doesn't return all documents. From a product within this Org, I created a new document and set the state to release but this query is not picking it up. I have attached the query and any help would be greatly. I am fairly new to Oracle and the Windchill DB schema so forgive my sloppy query!

I did look on the PTC support site and found nothing.

Thank you,
Eric

[sdrzewiczewski]

Why can't this be done in Report Manager? What limitation did you run into?

[MikeLockwood]

No need to use SQL to the database.
Attached is a query builder report that does this except for the Organization. Returns all Revisions. Here is the SQL that the Report builder creates and uses.

[cid:image002.png@01CF8F8C.25520780]

Accepts Number input - can set up to accept any other input.
Note: Number included as a 2nd column in order to provide it as a user-enterable parameter - redundant output. The first column is a hyperlink to the Document.


* From Document

o Select: Number

o Select: Name

o Select: Version

o Criteria: Latest Iteration (shows Release of all Revisions if done this way)

* From Lifecycle History joined to Document on Object History, set criteria

o Constant: State = Released

o Constant: Action = Enter_Phase

o Parameter: Created (got to this state) date range

example
[cid:image001.png@01CF8F8B.D39DE440]
[cid:image003.png@01CF8F8C.25520780]

[sdrzewiczewski]

I would assume you just need to join an additional table for the Org.

[atre.shreyas]

Eric,

Below SQL will return all Documents released in Last 20 Days.

) AND (A0.action = 'Enter_Phase'))) AND ((A3.idA3A5 = A1.idA2A2) AND (A3.idA3B5 = A0.idA2A2)AND (A1.idA3containerReference = A2.idA2A2)) and A1.idA3masterReference=A11.idA2A2

Thanks,