QC Query – Get bugs related to test cases

Posted on by

We were talking about ALM (formerly QC) earlier this week, and one of the test managers brought something up. So there you have your test case, and your test case will have test steps. During test execution, while at a particular test step, you can log a bug and that bug will be linked to the test step — but not to the test case. So if you look at your test case in the execution grid, it won’t have that link icon indicating that there’s a related bug. You can open each test case one-by-one and then view the indirect links, but that would be time-consuming.

We’re not sure whether the newer version of ALM would make the connection more obvious, and whether it’ll be easier to pull out info on the bugs related to the test cases. But even if it were, it might take some time for the upgrade to be available. One workaround is to go ahead and manually link the bug to the test case.

I tried sitting on it, and I came up with a query. It’s not a catch all solution but one can use it as a starting point. Given a test folder, it retrieves the test sets under that folder and also the test cases under the test sets. And based on the test cases you have, it retrieves the bugs that are linked to the test cases directly, or linked to test steps that are part of those test cases.

SELECT A.*, LN_BUG_ID, BG_STATUS
FROM
(SELECT CYCLE.CY_CYCLE_ID, CYCLE.CY_CYCLE, TEST.TS_TEST_ID, TEST.TS_NAME, TESTCYCL.TC_STATUS
 FROM CYCLE, TESTCYCL, TEST
 WHERE TESTCYCL.TC_CYCLE_ID = CYCLE.CY_CYCLE_ID
   AND TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
   AND CYCLE.CY_FOLDER_ID = 103) A   -- NEED TO SPECIFY THE FOLDER ID (FROM CYCL_FOLD)
LEFT OUTER JOIN
(SELECT DISTINCT LN_BUG_ID, BG_STATUS, TEST_ID FROM (
    SELECT LINK.LN_BUG_ID, BUG.BG_STATUS, LINK.LN_ENTITY_TYPE, LINK.LN_ENTITY_ID, 
    COALESCE(STEP.ST_TEST_ID, TESTCYCL.TC_TEST_ID) AS TEST_ID
    FROM LINK
    INNER JOIN BUG 
    ON LINK.LN_BUG_ID = BUG.BG_BUG_ID
    LEFT OUTER JOIN STEP
    ON LINK.LN_ENTITY_ID = STEP.ST_ID AND LINK.LN_ENTITY_TYPE = 'STEP'
    LEFT OUTER JOIN TESTCYCL
    ON LINK.LN_ENTITY_ID = TESTCYCL.TC_TESTCYCL_ID AND LINK.LN_ENTITY_TYPE = 'TESTCYCL')) B
ON A.TS_TEST_ID = B.TEST_ID
ORDER BY CY_CYCLE_ID, TS_TEST_ID, LN_BUG_ID

Leave a comment