Skip Navigation LinksHome Page > Forums > SpiraTeam Forums > Spira Custom Reports > Subqueries seem being not...

Subqueries seem being not working

Monday, October 24, 2022

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

4 Replies

Tuesday, October 25, 2022

inflectra.david

re: dterragni Monday, October 24, 2022

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

Tuesday, October 25, 2022

dterragni

re: inflectra.david Tuesday, October 25, 2022

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.

Daniele

Saturday, October 29, 2022

inflectra.david

re: dterragni Tuesday, October 25, 2022