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!
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
--BG_STATUS NOT IN ('Closed','Defect Resolved','Rejected')
--BG_STATUS NOT IN ('Closed','Rejected')
ORDER BY BG_BUG_ID
Re: SQL Query to return specific tests and their associated defects (1-many)
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,