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.
.
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.
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)
