Database schema joins

i had few queries as  mentioned below

1) i am trying to get the “part type” for the item which is value for subclass column in item table and using below query i am getting multiple rows with different values and different property id column.One of these values is pointing to the part type.Please let me know is their any other table which i have to refer for the same.

select * from Propertytable where PARENTID in
(select SUBCLASS FROM item where ITEM_NUMBER in (‘DUMMY-TR’));

2)i am facing the same issue when trying to obtain the value for workflow name and status name for the psr using below queries.Is this right table to refer  or any other relationships are available in db to get the above information.

select * from Propertytable where PARENTID in
(select WORKFLOW_ID FROM PSR where PSR_NO in (‘PR00242’));

select * from Propertytable where PARENTID in
(select STATUS FROM PSR where PSR_NO in (‘PR00242’));

Is property  table right  to get all the above information???

3)where can we get the workflow history data like entrydate/exitdate for Objects like PSR,QCR.
.

Agile User Asked on January 22, 2016 in Agile PLM (v9),   Product Collaboration.
Add Comment
2 Answer(s)

What version of Agile are you working in???  The name of the subclass will be in NODETABLE, what is in PROPERTYTABLE are various properties for the subclass. PROPERTYID defines what the property is, and VALUE or SELECTION contains the value itself. Same for the workflow and status, the name will be in NODETABLE. For the most part, you do not need to be concerned with what is in PROPERTYTABLE, just get the name of  the subclass/workflow/status from NODETABLE. Workflow history is mostly in WORKFLOW_PROCESS table, but who approved/rejected what is held in SIGNOFF, and it also links to CHANGE_HISTORY.
 Please note that the Agile schema is pretty complicated, and there is  no public documentation on how it works.

Agile Angel Answered on January 22, 2016.
Add Comment

For the same class, you have multiple property in propertytable. The Property id 38 is the classname.
However, as mentioned by Kevin, it is not a good idea to use Agile DB to get some queries because it can be changed installing a new patch/version and it is not documented. You can use the SDKs quickly to get this info (slower but more reliable)

Agile Angel Answered on January 24, 2016.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.