Bug-Test-Requirements List per Release

 

The query below extracts Bug linked to a specific Release with Test and Requirements info.

 

Prerequisit: Defect must be created during the Execution of a Run Step. It's very important to understand which are the object that can be releated with the BUG:

 

  • STEP (in Run Mode)
  • RUN
  • TestInstance
  • Test
  • Requirement

 

So the analyze done on the LINK table consists, in this case, on ENTITY_TYPE = 'STEP'.

 

Suppose the Release ID is 16.

 

SELECT B.BG_BUG_ID AS BUG_ID,
              B.BG_SEVERITY AS SEVERITY,
              B.BG_STATUS AS BUG_STATUS,
              A.REL_NAME AS RELEASE,
              C.RQ_REQ_ID AS ID_REQ,
              C.RQ_REQ_NAME AS REQ_NAME,
              C.RQ_REQ_STATUS AS REQ_STATUS,
              D.TS_TEST_ID AS ID_TEST,
              D.TS_NAME AS TEST_NAME,
              D.TS_DESCRIPTION AS TEST_DESCRIPTION,
              D.TS_PATH AS TEST_PATH,
              E.TC_STATUS AS EXECUTION_SATUS
FROM BUG B
     JOIN (SELECT REL_ID,

                        REL_NAME FROM RELEASES) AS A

             ON A.REL_ID = B.BG_DETECTED_IN_REL
     JOIN (SELECT LN_ENTITY_ID,

                        LN_BUG_ID,

                        LN_ENTITY_TYPE FROM LINK) AS L

             ON L.LN_BUG_ID = B.BG_BUG_ID AND L.LN_ENTITY_TYPE = 'STEP'
     JOIN (SELECT ST_ID,

                        ST_RUN_ID FROM STEP) AS S

             ON S.ST_ID = L.LN_ENTITY_ID
     JOIN (SELECT RN_RUN_ID,

                        RN_TESTCYCL_ID,

                        RN_TEST_ID FROM RUN) AS R

             ON R.RN_RUN_ID = S.ST_RUN_ID
     JOIN (SELECT TC_TESTCYCL_ID,

                        TC_TEST_ID,

                        TC_STATUS FROM TESTCYCL) AS E

              ON E.TC_TESTCYCL_ID = R.RN_TESTCYCL_ID
     JOIN (SELECT TS_TEST_ID,

                        TS_NAME,

                        TS_DESCRIPTION,

                        TS_PATH FROM TEST) AS D

              ON D.TS_TEST_ID = E.TC_TEST_ID
     JOIN (SELECT RC_REQ_ID,

                        RC_ENTITY_ID,

                        RC_ENTITY_TYPE FROM REQ_COVER) AS COV

              ON COV.RC_ENTITY_TYPE = 'TEST' AND

                   COV.RC_ENTITY_ID = D.TS_TEST_ID
     JOIN (SELECT RQ_REQ_ID,

                        RQ_REQ_NAME,

                        RQ_REQ_STATUS FROM REQ) AS C

              ON C.RQ_REQ_ID = COV.RC_REQ_ID
WHERE B.BG_DETECTED_IN_REL = 16