Hello, I am new to Spira and while I have some SQL experience, I have not used ESQL before. I keep getting an exception when trying a report syntax that worked in our previous tool. Ive spent some time searching and looking at examples, but have come up empty. I am hoping some ESQL expert can point me in the right direction. I am trying to get a list of test case ids from test cases that are nested two folders deep. I would like a query to specify both folders so have written this: select TC.TEST_CASE_ID as TestID, TC.NAME as Test_Name, TCF.TEST_CASE_FOLDER_ID as FolderID, TCF.NAME as Folder from SpiraTestEntities.R_TestCases as TC, SpiraTestEntities.R_TestCaseFolders as TCF where TC.PROJECT_ID = ${ProjectId} and TCF.NAME = and TC.TEST_CASE_FOLDER_ID = TCF.TEST_CASE_FOLDER_ID and TCF.PARENT_TEST_CASE_FOLDER_ID = (select R.TEST_CASE_FOLDER_ID from SpiraTestEntities.R_TestCaseFolders as R where R.NAME = ) However, this produces the following error: System.Data.EntitySqlException: The argument types Edm.Int32 and Transient.collection[Transient.rowtype[(TEST_CASE_FOLDER_ID,Edm.Int32(Nullable=True,DefaultValue=))](Nullable=True,DefaultValue=)] are incompatible for this operation. Near equals expression... Thanks, Shari 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/2267.aspxthreadId=2267Shari Ruppel (shari.ruppel@mckesson.com) Hello, I am new to Spira and while I have some SQL experience, I have not used ESQL before. I keep getting an exception when trying a report syntax that worked in our previous tool. Ive spent some time searching and looking at examples, but have come up empty. I am hoping some ESQL expert can point me in the right direction. I am trying to get a list of test case ids from test cases that are nested two folders deep. I would like a query to specify both folders so have written this: select TC.TEST_CASE_ID as TestID, TC.NAME as Test_Name, TCF.TEST_CASE_FOLDER_ID as FolderID, TCF.NAME as Folder from SpiraTestEntities.R_TestCases as TC, SpiraTestEntities.R_TestCaseFolders as TCF where TC.PROJECT_ID = ${ProjectId} and TCF.NAME = and TC.TEST_CASE_FOLDER_ID = TCF.TEST_CASE_FOLDER_ID and TCF.PARENT_TEST_CASE_FOLDER_ID = (select R.TEST_CASE_FOLDER_ID from SpiraTestEntities.R_TestCaseFolders as R where R.NAME = ) However, this produces the following error: System.Data.EntitySqlException: The argument types Edm.Int32 and Transient.collection[Transient.rowtype[(TEST_CASE_FOLDER_ID,Edm.Int32(Nullable=True,DefaultValue=))](Nullable=True,DefaultValue=)] are incompatible for this operation. Near equals expression... Thanks, Shari Mon, 14 Dec 2020 21:53:32 -05002020-12-19T18:45:59-05:00/Support/Forum/spirateam/reports/2267.aspxmessageId=3901David J (adam.sandman+support@inflectra.com) Hi Shari Please refer to this general guide. I think your JOIN syntax is not correct: https://spiradoc.inflectra.com/Reporting/Custom-Graph-Tutorial/#understanding-entity-sql-esql https://spiradoc.inflectra.com/Reporting/Custom-Graph-Tutorial/#the-join-clauses ESQL uses the ANSI join syntax (from X join Z on X.Y = Z.Y rather than where X.Y = Z.Y) Regards David Tue, 15 Dec 2020 15:40:30 -05002020-12-15T15:40:30-05:00/Support/Forum/spirateam/reports/2267.aspx#reply3901messageId=3902Shari Ruppel (shari.ruppel@mckesson.com) Hi David, Based on your suggestion, I changed the query to this: select TC.TEST_CASE_ID as TestID, TC.NAME as Test_Name, TCF.TEST_CASE_FOLDER_ID as FolderID, TCF.NAME as Folder from SpiraTestEntities.R_TestCases as TC join SpiraTestEntities.R_TestCaseFolders as TCF on TC.TEST_CASE_FOLDER_ID = TCF.TEST_CASE_FOLDER_ID where TC.PROJECT_ID = ${ProjectId} and TCF.NAME = and TCF.PARENT_TEST_CASE_FOLDER_ID = (select R.TEST_CASE_FOLDER_ID from SpiraTestEntities.R_TestCaseFolders as R where R.NAME = ) but still get the same issue. I am guessing that there is a different syntax that is needed for query in the where clause, like some sort of recursive join. Im still researching, but any additional help would be appreciated. Thanks, Shari Tue, 15 Dec 2020 21:18:34 -05002020-12-15T21:18:34-05:00/Support/Forum/spirateam/reports/2267.aspx#reply3902messageId=3903Shari Ruppel (shari.ruppel@mckesson.com) I finally got this by using the in operator instead of = and specifying the keyword value in the nested select. Here is the final result: select TC.TEST_CASE_ID as TestID, TC.NAME as Test_Name, TCF.TEST_CASE_FOLDER_ID as FolderID, TCF.NAME as Folder from SpiraTestEntities.R_TestCases as TC join SpiraTestEntities.R_TestCaseFolders as TCF on TC.TEST_CASE_FOLDER_ID = TCF.TEST_CASE_FOLDER_ID where TC.PROJECT_ID = ${ProjectId} and TCF.NAME = and TCF.PARENT_TEST_CASE_FOLDER_ID in (select value R.TEST_CASE_FOLDER_ID from SpiraTestEntities.R_TestCaseFolders as R where R.NAME = ) Wed, 16 Dec 2020 16:18:02 -05002020-12-16T16:18:02-05:00/Support/Forum/spirateam/reports/2267.aspx#reply3903messageId=3906David J (adam.sandman+support@inflectra.com) Thanks! Sat, 19 Dec 2020 18:45:59 -05002020-12-19T18:45:59-05:00/Support/Forum/spirateam/reports/2267.aspx#reply3906