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