use of Sub-Queries in Custom Section

Monday, October 28, 2019
Avatar

Hello,

I tried using a sub-query in the SQL of a Custom report Section but I keep getting generic errors when I try to preview:

System.Data.EntitySqlException: The query syntax is not valid. Near term '1', line 17, column 15.

Can I use Sub-Queries or is this restricted in Custom SQL Section?

My SQL is below:

select TC.TEST_CASE_ID,
    TC.NAME AS TESTCASE_NAME,
    TC.DESCRIPTION AS TESTCASE_DESCRIPTION,
    TC.EXECUTION_DATE as TESTCASE_EXECUTION_DATE,
    TC.CREATION_DATE as TESTCASE_CREATION_DATE,
    TC.LAST_UPDATE_DATE as TESTCASE_LAST_UPDATE_DATE,
    TC.AUTOMATION_ENGINE_NAME as TESTCASE_AUTOMATION_ENGINE_NAME,
    TC.EXECUTION_STATUS_NAME as TESTCASE_EXECUTION_STATUS_NAME,
    TC.AUTHOR_NAME as TESTCASE_AUTHOR_NAME,
    TC.OWNER_NAME as TESTCASE_OWNER_NAME,
    TC.TEST_CASE_STATUS_NAME as TESTCASE_TEST_CASE_STATUS_NAME,
    TC.TEST_CASE_TYPE_NAME as TESTCASE_TEST_CASE_TYPE_NAME,
    TC.TEST_CASE_PRIORITY_NAME as TESTCASE_TEST_CASE_PRIORITY_NAME,
    TC.CUST_01 as TESTCASE_CUST_01,
    TC.CUST_02 as TESTCASE_CUST_02,
    TC.CUST_03 as TESTCASE_CUST_03,
    (SELECT TOP 1 TC_C1.NAME from SpiraTestEntities.R_CustomListValues as TC_C1 WHERE cast(TC.CUST_02 as int32) = TC_C1.CUSTOM_PROPERTY_VALUE_ID and TC.PROJECT_ID = TC_C1.PROJECT_ID) as TESTCASE_CUST_04,
    
    TS.TEST_STEP_ID,
    TS.POSITION AS TESTSTEP_STEP,
    TS.DESCRIPTION as TESTSTEP_DESCRIPTION,
    TS.EXECUTION_STATUS_NAME as TESTSTEP_EXECUTION_STATUS_NAME,
    TS.EXPECTED_RESULT as TESTSTEP_EXPECTED_RESULT,
    TS.SAMPLE_DATA as TESTSTEP_SAMPLE_DATA,
    
    RTC.REQUIREMENT_ID,
    
    RQ.NAME AS REQUIREMENT_NAME,
    RQ.DESCRIPTION AS REQUIREMENT_DESCRIPTION,
    RQ.REQUIREMENT_STATUS_NAME as REQUIREMENT_REQUIREMENT_STATUS_NAME,
    RQ.REQUIREMENT_TYPE_NAME as REQUIREMENT_TYPE_NAME,
    RQ.IMPORTANCE_NAME as REQUIREMENT_PRIORITY,
    RQ.RELEASE_VERSION_NUMBER as REQUIREMENT_RELEASE_VERSION_NUMBER,
    RQ.CUST_01 as REQUIREMENT_CUST_01

from SpiraTestEntities.R_TestCases as TC 
left join SpiraTestEntities.R_TestSteps as TS on TC.TEST_CASE_ID = TS.TEST_CASE_ID
left join SpiraTestEntities.R_RequirementTestCases as RTC on RTC.TEST_CASE_ID = TC.TEST_CASE_ID
left join SpiraTestEntities.R_Requirements as RQ on RTC.REQUIREMENT_ID = RQ.REQUIREMENT_ID
where TC.PROJECT_ID = ${ProjectId}

2 Replies
Monday, October 28, 2019
Avatar
re: shurix Monday, October 28, 2019

In general you can use sub-queries, but ESQL is subtly different than database SQL.

We have a new blog series that will be providing more information on reporting and queries - https://www.inflectra.com/Ideas/Entry/creating-custom-graphs-with-spira-part-1-911.aspx

Regards
David

Tuesday, October 25, 2022
Avatar
re: inflectra.david Monday, October 28, 2019

For those reading this, the sub-query needs to be in the join section, not the select section:

    (SELECT TOP 1 TC_C1.NAME from SpiraTestEntities.R_CustomListValues as TC_C1 WHERE cast(TC.CUST_02 as int32) = TC_C1.CUSTOM_PROPERTY_VALUE_ID and TC.PROJECT_ID = TC_C1.PROJECT_ID) as TESTCASE_CUST_04,

A better example is here:

https://www.inflectra.com/Support/Forum/spirateam/reports/2698.aspx#reply5777

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, October 28, 2019
  • Last Reply: Tuesday, October 25, 2022
  • Replies: 2
  • Views: 2214