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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s