ESQL error

Monday, December 14, 2020
Avatar

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

 

4 Replies
Tuesday, December 15, 2020
Avatar
re: sruppel Monday, December 14, 2020

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

Tuesday, December 15, 2020
Avatar
re: inflectra.david Tuesday, December 15, 2020

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 = "<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.

Thanks, 

Shari

  

Wednesday, December 16, 2020
Avatar
re: sruppel Tuesday, December 15, 2020

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 = "<Test Case Folder 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 = "<Parent Folder Name>")
 

Saturday, December 19, 2020
Avatar
re: sruppel Wednesday, December 16, 2020

Thanks!

Statistics
  • Started: Monday, December 14, 2020
  • Last Reply: Saturday, December 19, 2020
  • Replies: 4
  • Views: 474