[resolved] Common Table Expressions not working with HSQLDB version 5.4

[_AnonymousUser]

I was understanding that HSQLDB supported CTE's (recursive at that).
I was trying to use in memory HSQLDB
But this query:
with manager_list as
(
SELECT distinct ManagerID FROM manulife_data
)
SELECT
dr.ManagerID
, m1.Cost_Center_ID+'-'+m1.First_Name+' '+m1.Last_Name as Parent_Name
, m1.Employee_Number as Parent_Code
, m1.Cost_Center_ID+'-'+m1.First_Name+' '+m1.Last_Name as Department_Name
, m1.Employee_Number as Department_Code
FROM manager_list as dr
inner join manulife_data as m1
on dr.ManagerID = m1.Employee_Number
gets me the error
unexpected token WITH in statement
this works just fine:
SELECT
dr.ManagerID
, m1.Cost_Center_ID+'-'+m1.First_Name+' '+m1.Last_Name as Parent_Name
, m1.Employee_Number as Parent_Code
, m1.Cost_Center_ID+'-'+m1.First_Name+' '+m1.Last_Name as Department_Name
, m1.Employee_Number as Department_Code
FROM (SELECT distinct ManagerID FROM manulife_data) as dr
inner join manulife_data as m1
on dr.ManagerID = m1.Employee_Number

[_AnonymousUser · Accepted solution]

Ok,
Solved it. I went and downloaded version 2.3.2 of hsqldb.jar and subqueries work.
Ken