Pages

Tuesday, September 3

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.

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.

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