Quality Center test instances /test cases that are blocked and corresponding linked defects

One time or the other during the test execution phase, one comes across a question on how to find the test cases that are blocked by defect. This is key because

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

1. First time Fix faliures or Reopen defects
2. How to retrieve Quality Center bug details and Audit tables information
3. How to retrieve QC status history for closed defect
4. How to retrieve test instances and test sets within particular folder
5. How to retrieve requirements and test coverage and linked test cases