Skip Navigation LinksHome Page > Forums > SpiraTeam Forums > Spira Custom Reports > ESQL error
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. I've 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 = "<Test Case Folder 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 = "<Parent Folder 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
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
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 TCjoin SpiraTestEntities.R_TestCaseFolders as TCF on TC.TEST_CASE_FOLDER_ID = TCF.TEST_CASE_FOLDER_IDwhere TC.PROJECT_ID = ${ProjectId} and TCF.NAME = "<Test Case Folder Name>" and TCF.PARENT_TEST_CASE_FOLDER_ID = (select R.TEST_CASE_FOLDER_ID from SpiraTestEntities.R_TestCaseFolders as R where R.NAME = "<Parent Folder 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. I'm still researching, but any additional help would be appreciated.
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 TCjoin SpiraTestEntities.R_TestCaseFolders as TCF on TC.TEST_CASE_FOLDER_ID = TCF.TEST_CASE_FOLDER_IDwhere TC.PROJECT_ID = ${ProjectId} and TCF.NAME = "<Test Case Folder Name>" and TCF.PARENT_TEST_CASE_FOLDER_ID in (select valueR.TEST_CASE_FOLDER_ID from SpiraTestEntities.R_TestCaseFolders as R where R.NAME = "<Parent Folder Name>")
Thanks!
And if you have any questions, please email or call us at +1 (202) 558-6885
