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

One Star

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

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

Accepted Solutions
One Star

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

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

All Replies
One Star

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

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