I created a query for test reporting where I want to show, per each release, the linked test sets, test cases, last execution run and the total number of active defects linked to each test. I designed a query with a subquery calculating the total number of defects, but the subquery shows nothing in the report (empty column), whilst it shows System.Data.Query.ResultAssembly.BridgeDataReader in the preview results section. I read about an old similar question in this forum, but the answer was not satisfying to me and was not suitable to my case. How can I write the query in ESQL in order to get the information? My sub query is colored below: SELECT RL.name AS Release, TS.release_version_number AS ReleaseVs, ... ( SELECT value COUNT(0) FROM SpiraTestEntities.R_TestCaseIncidents AS BG WHERE BG.test_case_id = TSTC.test_case_id AND BG.is_open_status = True ) AS OpenDefects FROM SpiraTestEntities.R_TestSets AS TS JOIN SpiraTestEntities.R_Releases AS RL ON TS.release_id = RL.release_id AND TS.is_deleted = False ... LEFT JOIN SpiraTestEntities.R_TestRuns AS RN ON TSTC.test_set_test_case_id = RN.test_set_test_case_id AND RN.is_deleted = False ... WHERE ... ORDER BY ... Thanks, Daniele en-US(C) Copyright 2006-2026 Inflectra Corporation.support@inflectra.com/Computers/Software/Project_Management//Computers/Software/Quality_Assurance/KronoDesksupport@inflectra.comhttp://www.inflectra.com/kronodesk/forums/threads120/Support/Forum/spirateam/reports/2698.aspxthreadId=2698Daniele Terragni (d.terragni@quence.it)sub query does not return data I created a query for test reporting where I want to show, per each release, the linked test sets, test cases, last execution run and the total number of active defects linked to each test. I designed a query with a subquery calculating the total number of defects, but the subquery shows nothing in the report (empty column), whilst it shows System.Data.Query.ResultAssembly.BridgeDataReader in the preview results section. I read about an old similar question in this forum, but the answer was not satisfying to me and was not suitable to my case. How can I write the query in ESQL in order to get the information? My sub query is colored below: SELECT RL.name AS Release, TS.release_version_number AS ReleaseVs, ... ( SELECT value COUNT(0) FROM SpiraTestEntities.R_TestCaseIncidents AS BG WHERE BG.test_case_id = TSTC.test_case_id AND BG.is_open_status = True ) AS OpenDefects FROM SpiraTestEntities.R_TestSets AS TS JOIN SpiraTestEntities.R_Releases AS RL ON TS.release_id = RL.release_id AND TS.is_deleted = False ... LEFT JOIN SpiraTestEntities.R_TestRuns AS RN ON TSTC.test_set_test_case_id = RN.test_set_test_case_id AND RN.is_deleted = False ... WHERE ... ORDER BY ... Thanks, Daniele Mon, 24 Oct 2022 07:50:06 -04002024-08-19T14:47:15-04:00/Support/Forum/spirateam/reports/2698.aspxmessageId=5777David J (adam.sandman+support@inflectra.com) Hi Daniele There are some issues with the query, you need to have all of the collections references where they are used. Heres the corrected query (I think): SELECT RL.name AS Release, TS.release_version_number AS ReleaseVs, OpenDefects.bug_count FROM SpiraTestEntities.R_TestSets AS TS JOIN SpiraTestEntities.R_Releases AS RL ON TS.release_id = RL.release_id AND TS.is_deleted = False JOIN SpiraTestEntities.R_TestSetTestCases AS TSTC ON TS.test_set_id = TSTC.test_set_id JOIN ( SELECT COUNT(0) as bug_count, TSTC.test_set_test_case_id FROM SpiraTestEntities.R_TestCaseIncidents AS BG JOIN SpiraTestEntities.R_TestSetTestCases AS TSTC ON BG.test_case_id = TSTC.test_case_id WHERE BG.is_open_status = True GROUP BY TSTC.test_set_test_case_id ) AS OpenDefects on OpenDefects.test_set_test_case_id = TSTC.test_set_test_case_id LEFT JOIN SpiraTestEntities.R_TestRuns AS RN ON TSTC.test_set_test_case_id = RN.test_set_test_case_id AND RN.is_deleted = False Regards David Tue, 25 Oct 2022 09:17:11 -04002022-10-25T09:17:11-04:00/Support/Forum/spirateam/reports/2698.aspx#reply5777messageId=5780Daniele Terragni (d.terragni@quence.it) Thank you David, it works fine, now. Im not familiar with ESQL, I wrote my first script last Wednesday and I have a lot to learn. I think I understood the construct of the embedded query. Thanks again. Daniele Tue, 25 Oct 2022 11:26:09 -04002022-10-25T11:26:09-04:00/Support/Forum/spirateam/reports/2698.aspx#reply5780messageId=5787David J (adam.sandman+support@inflectra.com) You are most welcome. Sat, 29 Oct 2022 17:26:50 -04002022-10-29T17:26:50-04:00/Support/Forum/spirateam/reports/2698.aspx#reply5787