ESQL String Aggregate

Monday, May 23, 2022
Avatar

Hello,

I am trying to create an ESQL query that groups  the results by the Test Case ID. For example, if I have the following table:

Test Case IDRequirement
1RQ1
1RQ2
2RQ3
2RQ4

 

I want an ESQL query the groups the Requirements by Test Case ID and outputs:

Test Case IDRequirement
1RQ1, RQ2
2RQ3, RQ4

 

For SQL, it appears that the query would be:

SELECT TEST_CASE_ID, STRING_AGG(REQUIREMENT_NAME,',') AS REQUIREMENTS

FROM SpiraTestEntities.R_RequirementTestCases

GROUP BY TEST_CASE_ID

However, ESQL removed the STRING_AGG function. Is there another way to do this?

3 Replies
Tuesday, May 24, 2022
Avatar
re: jacob.d.carbajal Monday, May 23, 2022

I don't think ESQL supports that I'm afraid.

You may want to simply group by requirement name and then use XSLT to do the further aggregating and grouping.

Thursday, February 8, 2024
Avatar
re: inflectra.david Tuesday, May 24, 2022

I am looking for a solution for exactly this.

@jacob, did you get a resolution to this issue?

Wednesday, February 14, 2024
Avatar
re: Swedlund Thursday, February 8, 2024

Fixed it with XSLT 1.0 as suggested.

For anyone else getting into this issue, see my code below.

XSLT ( Group Requirements by Test Case ID):

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl">
  <xsl:output method="xml" indent="yes"/>      
  <xsl:key name="groups" match="/RESULTS/ROW" use="ID" />
  <xsl:template match="/RESULTS">
  	<table class="DataGrid">
  		<colgroup>
	     <col width="75px"/>
	     <col/>
	     <col/>
	     <col width="45px"/>
	     <col width="48px"/>
	     <col/>
	   	</colgroup>
		<tr>
			<th>#</th>
			<th>NAME</th>
			<th>REQUIREMENT</th>
			<th>STATUS</th>
			<th>VERSION</th>
			<th>BUG</th>
		</tr>
    	<xsl:apply-templates select="ROW[generate-id() = generate-id(key('groups', ID)[1])]"/>
    </table>
  </xsl:template>
  <xsl:template match="ROW">
      <tr id="{ID}">
          <td valign="top">
          	<table>
		        <tr>
		          <td valign="top" style="border: 0;">TC:<xsl:value-of select="ID"/></td>
		        </tr>
		        <tr>
		          <td valign="top" style="border: 0;">TR:<xsl:value-of select="TR"/></td>
		        </tr>
		 	</table>
          </td>
          <td valign="top"><xsl:value-of select="NAME"/></td>
          <td valign="top">
          	<xsl:if test="REQUIREMENT != ''">
	          	<table>
			      <xsl:for-each select="key('groups', ID)">
			        <tr>
			          <td valign="top" style="border: 0;"><xsl:value-of select="REQUIREMENT"/></td>
			        </tr>
			      </xsl:for-each>
			 	</table>
		 	</xsl:if>
          </td>
          <td valign="top"><xsl:value-of select="STATUS"/></td>
          <td valign="top"><xsl:value-of select="VERSION"/></td>
          <td valign="top">
          	<xsl:if test="BUG != ''">
	          	<table>
			        <tr>
			          <td valign="top" style="border: 0;"><xsl:value-of select="BUG"/></td>
			        </tr>
			        <tr>
			          <td valign="top" style="border: 0;"><xsl:value-of select="JIRA_LINK"/></td>
			        </tr>
			 	</table>
		    </xsl:if>
          </td>
        </tr>
  </xsl:template>
</xsl:stylesheet>

And here's my ESQL query to gather the info required for an TRF report (TC->REQ->BUG tracabillity)

SELECT 

   TC.TEST_CASE_ID AS ID,
   TR3.TEST_RUN_ID AS TR,
   TC.NAME,
   RQ.REQUIREMENT_NAME AS REQUIREMENT,
   TR3.EXECUTION_STATUS_NAME AS STATUS,
   TR3.RELEASE_ID,
   TR3.RELEASE_VERSION_NUMBER AS VERSION,
   I.NAME AS BUG,
   I.CUST_01 AS JIRA_LINK

FROM SpiraTestEntities.R_TestCases AS TC

   JOIN (SELECT
            TR2.TEST_CASE_ID,
            MAX(TR2.TEST_RUN_ID) AS TEST_RUN_ID,
            MAX(TR2.RELEASE_ID) AS RELEASE_ID
          FROM SpiraTestEntities.R_TestRuns AS TR2
          WHERE TR2.END_DATE IS NOT NULL
          GROUP BY TR2.TEST_CASE_ID
         ) AS TR ON TC.TEST_CASE_ID=TR.TEST_CASE_ID

   JOIN SpiraTestEntities.R_TestRuns AS TR3 ON TR3.TEST_RUN_ID=TR.TEST_RUN_ID

   LEFT JOIN SpiraTestEntities.R_RequirementTestCases AS RQ ON TC.TEST_CASE_ID = RQ.TEST_CASE_ID

   LEFT JOIN SpiraTestEntities.R_TestRunIncidents AS TRI ON TR.TEST_RUN_ID=TRI.TEST_RUN_ID

   LEFT JOIN SpiraTestEntities.R_Incidents AS I ON TRI.INCIDENT_ID=I.INCIDENT_ID


WHERE 
  TC.PROJECT_ID = ${ProjectId} AND 
  TR.RELEASE_ID IN {${ReleaseAndChildIds}}

 

Spira Helps You Deliver Quality Software, Faster and With Lower Risk

And if you have any questions, please email or call us at +1 (202) 558-6885

 

Statistics
  • Started: Monday, May 23, 2022
  • Last Reply: Monday, February 26, 2024
  • Replies: 3
  • Views: 656