1. It helps to identify the defects that are blocking the test cases and highlight them to defect management teams
2. It helps to identify any defects that are closed recently and would have been inappropriately linked to a test case.
To determine this result, a SQL query can be executed in Quality center to retrieve the results. Generally any user can execute this query in Quality center by feature called excel query. Before moving onto this excel query , few points needs to be understood
Difference between test case and test instances : A single test case can be extracted into multiple test sets and also within same test set. If one pulls multiple tests of same test case into a test set, this becomes test instances of that particular test case.
Linked defect : This is the defect that is linked to a particular test instance . For instance multiple defects can be linked to many test cases or multiple test instance can be linked to many test cases.
*Note - Defects in Quality center can be linked at many levels to different entities.Such as one can link a defect to requirement and then change the status to blocked, etc. In this blog, I am referring to linked defects that are linked to test instances in the test set. This is generally many people follow during the test execution
In Quality center, the BUG ID for the linkage is stored in table called link. Bugs can be linked to test case, test instances, and requirements etc. These are called entities in quality center. As we are trying to find linked defects to test instances, the entity type for us in question is “TESTCYCL” (the table that holds test instances)
Below is the ER diagram for LINK table and BUG table.
TESTCYCL table consisting test instances is linked to TEST table (that holds test cases) by common field TEST_ID. Using these information we can construct the query as below which retrieves test cases, corresponding test instances and linked defect.
SELECT T.TS_NAME, TC.TC_TEST_INSTANCE,LN_BUG_ID FROM TEST T, TESTCYCL TC , LINK L WHERE T.TS_TEST_ID=TC.TC_TEST_ID -- Condition joining TEST (testcases) and TESTCYCL (Test instances) table AND L.LN_ENTITY_TYPE='TESTCYCL' -- This condition makes sure that defects linked to test instaces only need to retrieved AND TC.TC_TESTCYCL_ID=L.LN_ENTITY_ID -- Condition to join LINK and TESTCYCL table AND TC.TC_STATUS = 'BLOCKED'
In addition to above, it may be also necessary to know whether those defects are closed are not. Now that means in addition to retrieval of result from above query, you would also like to get the status of the defect . For this it requires to join BUG table. The below listed is the query that will also retrieve status of the defect.
SELECT T.TS_NAME, TC.TC_TEST_INSTANCE, LN_BUG_ID,B.BG_STATUS FROM TEST T, TESTCYCL TC , LINK L ,BUG B WHERE T.TS_TEST_ID=TC.TC_TEST_ID AND L.LN_ENTITY_TYPE='TESTCYCL' AND LN_BUG_ID = BG_BUG_ID AND TC.TC_TESTCYCL_ID=L.LN_ENTITY_ID AND TC.TC_STATUS = 'BLOCKED'
Apart from the above cases, you might also be interested to retrieve those test instances which are assigned to closed defects so that you can set the status back appropriately and remove the linkage. Below query that helps to achieve that
SELECT T.TS_NAME, TC.TC_TEST_INSTANCE, LN_BUG_ID,TC.TC_STATUS FROM TEST T, TESTCYCL TC , LINK L ,BUG B WHERE T.TS_TEST_ID=TC.TC_TEST_ID AND L.LN_ENTITY_TYPE='TESTCYCL' AND LN_BUG_ID = BG_BUG_ID AND TC.TC_TESTCYCL_ID=L.LN_ENTITY_ID AND B.BG_STATUS = 'Closed'
The other useful blogs related excel queries, Audit tables and QC history can be found in following locations