QC query to extract test cases that are linked only to closed defects

Question

I'm trying to extract the list of test instances from test lab that are linked to only closed,rejected and deferred defects. The test instances can be linked to more than one defect. In that case, I have to look through all the linked defects and if any of the defects are not closed/rejected/deferred, then the instance should not be picked. Following is the query that I use to extract all the test instances with linked defects, but this query also returns the open defects as well. Please help me in refining the query to eliminate the open defects.

Select
TESTCYCL.TC_TEST_ID as Test_ID,
TESTCYCL.TC_STATUS as TC_STATUS,
TESTCYCL.TC_EXEC_DATE As Actual_Execution_Date,
BUG.BG_BUG_ID as Defect_ID,  BUG.BG_USER_57  AS Project,
BUG.BG_STATUS as DEFECT_Status,
BUG.BG_USER_34 As Testing_Type,
BUG.BG_SEVERITY As Defect_Severity,
BUG.BG_USER_58 As Defect_Priority,
BUG.BG_DETECTION_DATE as Detection_Date



FROM BUG, TEST, V_LINK_TESTCYCL, TESTCYCL, CYCLE
WHERE BUG.BG_BUG_ID = V_LINK_TESTCYCL.LN_BUG_ID
  AND TESTCYCL.TC_TESTCYCL_ID = V_LINK_TESTCYCL.LN_TESTCYCL_ID
  AND TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
  AND CYCLE.CY_CYCLE_ID = TESTCYCL.TC_CYCLE_ID

//and (BUG.BG_STATUS='Closed' OR BUG.BG_STATUS='Deferred'  OR BUG.BG_STATUS='Rejected')

  order by   BUG.BG_BUG_ID

The result I'm getting is as follows: Excel -1

I need to exclude 1604 in the result as it is linked to open defect. It will be really great if anyone can help me in getting the expected result.

Thanks, Karthik S S

score 3 · Answer 1 · answered Oct 19 '16 at 13:06

Failed tests with closed defects based on the release cycle of your choice (I had 162 in the sql below)

enter code hereSELECT BG_BUG_ID,
BG_STATUS,
TC_STATUS,
TEST.TS_RESPONSIBLE,
TS_NAME,
TS_STATUS

from (BUG LEFT JOIN LINK ON BG_BUG_ID = LN_BUG_ID )
LEFT JOIN V_LINK_TESTCYCL on LINK.LN_BUG_ID = V_LINK_TESTCYCL.LN_BUG_ID
LEFT JOIN CYCLE ON LINK.LN_ENTITY_ID = CY_CYCLE_ID
LEFT JOIN TESTCYCL on V_LINK_TESTCYCL.LN_TESTCYCL_ID = TC_TESTCYCL_ID
left join test on tc_test_id = ts_test_id

Where

--BG_STATUS NOT IN ('Closed','Defect Resolved','Rejected')
BG_STATUS IN ('Closed','Cancel')
AND LINK.LN_ENTITY_TYPE = 'TESTCYCL'
AND TC_STATUS NOT IN ('Passed')
and BG_DETECTED_IN_RCYC = '162'
ORDER BY BG_BUG_ID

score 0 · Answer 2 · answered Apr 27 '16 at 14:18

How about you get all the TC_TEST_ID linked to Open Bugs and then exclude the tests from that list using subquery. e.g. below query.

Select
TESTCYCL.TC_TEST_ID as Test_ID,
TESTCYCL.TC_STATUS as TC_STATUS,
TESTCYCL.TC_EXEC_DATE As Actual_Execution_Date,
BUG.BG_BUG_ID as Defect_ID,  BUG.BG_USER_57  AS Project,
BUG.BG_STATUS as DEFECT_Status,
BUG.BG_USER_34 As Testing_Type,
BUG.BG_SEVERITY As Defect_Severity,
BUG.BG_USER_58 As Defect_Priority,
BUG.BG_DETECTION_DATE as Detection_Date
FROM BUG, TEST, V_LINK_TESTCYCL, TESTCYCL, CYCLE
WHERE BUG.BG_BUG_ID = V_LINK_TESTCYCL.LN_BUG_ID
  AND TESTCYCL.TC_TESTCYCL_ID = V_LINK_TESTCYCL.LN_TESTCYCL_ID
  AND TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
  AND CYCLE.CY_CYCLE_ID = TESTCYCL.TC_CYCLE_ID
  and TESTCYCL.TC_TEST_ID NOT IN (
  Select distinct TESTCYCL.TC_TEST_ID
   WHERE BUG.BG_BUG_ID = V_LINK_TESTCYCL.LN_BUG_ID
     AND TESTCYCL.TC_TESTCYCL_ID = V_LINK_TESTCYCL.LN_TESTCYCL_ID
     AND TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
     AND CYCLE.CY_CYCLE_ID = TESTCYCL.TC_CYCLE_ID
     and BUG.BG_STATUS='Open')
  order by   BUG.BG_BUG_ID

Hi Nimesh, I tried your query, it is just excluding the "Open" defect record. The remaining occurrences of the test instances that are linked to the closed defects are still retrieved. But I want to exclude the test instance entirely if any of the linked defect is in open. The instance ID should not be present in the report. — Karthik S S, Apr 28 '16 at 12:54

QC query to extract test cases that are linked only to closed defects

2 Answers2