https://community.qlik.com/t5/Talend-Studio/resolved-Common-Table-Expressions-not-working-with-HSQLDB/m-p/2222634#M16170 I was understanding that HSQLDB supported CTE's (recursive at that). <BR />I was trying to use in memory HSQLDB <BR />But this query: <BR />with manager_list as <BR />( <BR /> SELECT distinct ManagerID FROM manulife_data <BR />) <BR />SELECT <BR /> dr.ManagerID <BR /> , m1.Cost_Center_ID+'-'+m1.First_Name+' '+m1.Last_Name as Parent_Name <BR /> , m1.Employee_Number as Parent_Code <BR /> , m1.Cost_Center_ID+'-'+m1.First_Name+' '+m1.Last_Name as Department_Name <BR /> , m1.Employee_Number as Department_Code <BR />FROM manager_list as dr <BR />inner join manulife_data as m1 <BR />on dr.ManagerID = m1.Employee_Number <BR />gets me the error <BR />unexpected token WITH in statement <BR />this works just fine: <BR />SELECT <BR /> dr.ManagerID <BR /> , m1.Cost_Center_ID+'-'+m1.First_Name+' '+m1.Last_Name as Parent_Name <BR /> , m1.Employee_Number as Parent_Code <BR /> , m1.Cost_Center_ID+'-'+m1.First_Name+' '+m1.Last_Name as Department_Name <BR /> , m1.Employee_Number as Department_Code <BR />FROM (SELECT distinct ManagerID FROM manulife_data) as dr <BR />inner join manulife_data as m1 <BR />on dr.ManagerID = m1.Employee_Number Tue, 25 Mar 2014 18:29:05 GMT _AnonymousUser 2014-03-25T18:29:05Z https://community.qlik.com/t5/Talend-Studio/resolved-Common-Table-Expressions-not-working-with-HSQLDB/m-p/2222634#M16170 I was understanding that HSQLDB supported CTE's (recursive at that). <BR />I was trying to use in memory HSQLDB <BR />But this query: <BR />with manager_list as <BR />( <BR /> SELECT distinct ManagerID FROM manulife_data <BR />) <BR />SELECT <BR /> dr.ManagerID <BR /> , m1.Cost_Center_ID+'-'+m1.First_Name+' '+m1.Last_Name as Parent_Name <BR /> , m1.Employee_Number as Parent_Code <BR /> , m1.Cost_Center_ID+'-'+m1.First_Name+' '+m1.Last_Name as Department_Name <BR /> , m1.Employee_Number as Department_Code <BR />FROM manager_list as dr <BR />inner join manulife_data as m1 <BR />on dr.ManagerID = m1.Employee_Number <BR />gets me the error <BR />unexpected token WITH in statement <BR />this works just fine: <BR />SELECT <BR /> dr.ManagerID <BR /> , m1.Cost_Center_ID+'-'+m1.First_Name+' '+m1.Last_Name as Parent_Name <BR /> , m1.Employee_Number as Parent_Code <BR /> , m1.Cost_Center_ID+'-'+m1.First_Name+' '+m1.Last_Name as Department_Name <BR /> , m1.Employee_Number as Department_Code <BR />FROM (SELECT distinct ManagerID FROM manulife_data) as dr <BR />inner join manulife_data as m1 <BR />on dr.ManagerID = m1.Employee_Number Tue, 25 Mar 2014 18:29:05 GMT https://community.qlik.com/t5/Talend-Studio/resolved-Common-Table-Expressions-not-working-with-HSQLDB/m-p/2222634#M16170 _AnonymousUser 2014-03-25T18:29:05Z https://community.qlik.com/t5/Talend-Studio/resolved-Common-Table-Expressions-not-working-with-HSQLDB/m-p/2222635#M16171 Ok,<BR /> Solved it. I went and downloaded version 2.3.2 of hsqldb.jar and subqueries work.<BR />Ken Tue, 25 Mar 2014 20:12:39 GMT https://community.qlik.com/t5/Talend-Studio/resolved-Common-Table-Expressions-not-working-with-HSQLDB/m-p/2222635#M16171 _AnonymousUser 2014-03-25T20:12:39Z