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} en-US(C) Copyright 2006-2026 Inflectra Corporation.support@inflectra.com/Computers/Software/Project_Management//Computers/Software/Quality_Assurance/KronoDesksupport@inflectra.comhttp://www.inflectra.com/kronodesk/forums/threads120/Support/Forum/spirateam/reports/2102.aspxthreadId=2102Alex Soloviev (alex.soloviev@celerion.com) 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} Mon, 28 Oct 2019 15:21:35 -04002022-10-25T09:17:40-04:00/Support/Forum/spirateam/reports/2102.aspxmessageId=3673David J (adam.sandman+support@inflectra.com) 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 Mon, 28 Oct 2019 16:36:54 -04002019-10-28T16:36:54-04:00/Support/Forum/spirateam/reports/2102.aspx#reply3673messageId=5778David J (adam.sandman+support@inflectra.com) 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 Tue, 25 Oct 2022 09:17:40 -04002022-10-25T09:17:40-04:00/Support/Forum/spirateam/reports/2102.aspx#reply5778