QC Query – test cases with related requirements

Last week, a teammate posted a question in the forums asking for a way to extract a report from ALM that will list the test cases and the requirements that they are linked to. So although, there are probably built in reports, I figured that this is something that could also be extracted via an SQL query in the dashboard.

Here’s the query I came up with:

 SELECT --REQ.*, TEST.*
       TEST.TS_TEST_ID, TEST.TS_NAME,
       REQ.RQ_REQ_ID, REQ.RQ_REQ_NAME
 FROM  REQ_COVER,
       TEST,
       REQ
 WHERE REQ_COVER.RC_REQ_ID = REQ.RQ_REQ_ID
   AND REQ_COVER.RC_ENTITY_ID = TEST.TS_TEST_ID
   AND REQ_COVER.RC_ENTITY_TYPE = 'TEST'
 ORDER BY TEST.TS_TEST_ID

Another team mate shared a link to a similar forum topic where someone had already posted a similar query. The query in the other answer has the following additions:

  • It includes a join to ALL_LISTS on all_list.al_item_id = test.ts_subject — perhaps to filter by folder
  • It includes a join to REQ_TYPE on req_type.tpr_type_id = req.rq_type_id — perhaps to filter by requirement type
Advertisements

QC Query – Get bugs related to test cases

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