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
ORDER BY ...
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):
RL.name AS Release,
TS.release_version_number AS ReleaseVs,
SpiraTestEntities.R_TestSets AS TS
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
SELECT COUNT(0) as bug_count,
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
SpiraTestEntities.R_TestRuns AS RN ON TSTC.test_set_test_case_id = RN.test_set_test_case_id AND
RN.is_deleted = False
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