Welcome Serena Central users! CLICK HERE
The migration of the Serena Central community is currently underway. Be sure to read THIS MESSAGE to get your new login set up to access your account.
KB1 Absent Member.
Absent Member.
420 views

SQL Query to return specific tests and their associated defects (1-many)

 

Hello, I need to adjust the following query to find all failed test cases and their associated defects for a specific folder in the Test Lab.  Help please!  Thanks in advance!

 

 

The problem is that I can generate a Live Analysis for the Folder and see all the failed test cases and download them just fine.  However the defect ID do not get downloaded with them.  I need to generate a spreadsheet that has both, but only for one folder and everything underneath.  Thanks Again!

 

 

SELECT BG_BUG_ID,
BG_STATUS,
BG_PRIORITY,
LINK.LN_LINK_ID,
LINK.LN_BUG_ID,
LINK.LN_ENTITY_TYPE,
LINK.LN_ENTITY_ID,
LN_TESTCYCL_ID,
CY_CYCLE,
CY_CYCLE_ID,
TC_CYCLE_ID,
TC_TEST_ORDER,
TC_STATUS,
TS_NAME,
TS_STATUS,
TS_EXEC_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 NOT IN ('Closed','Rejected')
ORDER BY BG_BUG_ID

Tags (1)
0 Likes
1 Reply
Highlighted
Trusted Contributor.. bton99 Trusted Contributor..
Trusted Contributor..

Re: SQL Query to return specific tests and their associated defects (1-many)

Hi KB1,
Any relation to OB1?

 

I think you have already 'joined' to the test set (CYCLE)
So the link you need is to the 'Test Set Folder' table (CYCL_FOLD)
via the CY_FOLDER_ID.

 

At the end of your query add...( I THINK its an INNER JOIN)
...
INNER JOIN CYCL_FOLD ON CY_FOLDER_ID = CF_ITEM_ID
WHERE CF_ITEM_NAME = 'Your Folder Name'
....You can AND your other WHERE Clauses here...

 

Better still, add a parameter for "'Your Folder Name'" and then it can easily be used on any folder you like.

 

Also Just in case you use a "Folder of Folders of test sets" (i.e. Test sets inside folders inside PARENT folders)..
you can link out from CYCL_FOLD via CF_FATHER_ID by replacing the Where clause as above with:
 WHERE (CF_FATHER_ID IN
         (SELECT Distinct CF_ITEM_ID FROM CYCL_FOLD
          WHERE CF_ITEM_NAME = 'Your Parent Folder Name'  )
       )

 

Hope this helps,

TimG

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.