Skip Navigation LinksHome Page > Forums > SpiraTeam Forums > Spira Custom Reports > Subqueries seem being not...
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 TSJOIN 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
Hi Daniele
There are some issues with the query, you need to have all of the collections references where they are used.
Here's 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
Thank you David, it works fine, now.
I'm 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.
You are most welcome.
And if you have any questions, please email or call us at +1 (202) 558-6885
