You can translate the question and the replies:

Using CTE WITH clause for creating base view from query

I'm trying to use a CTE MS SQL query to flatten a dataset of employees and their managers. After looking through mutiple articles in the support section I was hoping I could use "create base view from query". This is giving an error saying - "Incorrect syntax near WITH". The reason is Denodo trying to do a select on top of this WITH which is not allowed in MS SQL. Any alternate work around for this? Thanks in advance SELECT t0.Employee_EIN, t0.Complete_Name, t0.Supervisor, t0.EmpLevel, t0.ReportsToJobCode, t0.ManagerName, t0.ManagerJobCode, t0.ManagerEIN FROM ( ;WITH UserCTE( Employee_EIN, Complete_Name, Supervisor, EmpLevel, ReportsToJobCode, ManagerName, ManagerJobCode, ManagerEIN) AS (SELECT Employee_EIN, Complete_Name, Supervisor, 0 AS EmpLevel, 0 ReportsToJobCode, CAST(Complete_Name AS VARCHAR(8000)) AS ManagerName, CAST(Job_Cd AS VARCHAR(8000)) AS ManagerJobCode, CAST(Employee_EIN AS VARCHAR(8000)) as ManagerEIN FROM [DWCORE].[DBO].[DIM_EMPLOYEE] WHERE Job_Cd = 'DC01' UNION ALL SELECT emp.Employee_EIN, emp.Complete_Name, emp.Supervisor, mgr.[EmpLevel] + 1, 0 ReportsToJobCode, CAST(mgr.ManagerName + CASE WHEN EmpLevel > 0 THEN '|' + emp.Complete_Name ELSE '' END AS VARCHAR(8000)) AS ManagerName, CAST(mgr.ManagerJobCode + CASE WHEN EmpLevel > 0 THEN '|' + emp.Job_Cd ELSE '' END AS VARCHAR(8000)) AS ManagerJobCode, CAST(mgr.ManagerEIN + CASE WHEN EmpLevel > 0 THEN '|' + emp.Employee_EIN ELSE '' END AS VARCHAR(8000)) AS ManagerEIN FROM [DWCORE].[DBO].[DIM_EMPLOYEE] as emp INNER JOIN UserCTE AS mgr ON emp.Supervisor = mgr.Employee_EIN WHERE emp.Supervisor IS NOT NULL) SELECT Employee_EIN, Complete_Name, Supervisor, EmpLevel, ReportsToJobCode, ManagerName, ManagerJobCode, ManagerEIN FROM UserCTE AS u option (maxrecursion 0); ) t0
user
19-03-2018 13:13:29 -0400

3 Answers

Hi, Your usage of WITH clause is not correct. I would do it in the following way. Using the following example you can implement the same with your scenario. WITH department_salary (deptno, totalpay) AS (SELECT deptno, SUM(salary) FROM EMP GROUP BY deptno) SELECT deptno FROM department_salary WHERE totalpay = ( SELECT max(totalpay) FROM department_salary ) To know more about how the WITH clause works refer to the section ["WITH Clause"](https://community.denodo.com/docs/html/browse/6.0/vdp/vql/queries_select_statement/with_clause/with_clause#with-clause) of the Virtual DataPort VQL Guide. Hope this helps.
Denodo Team
22-03-2018 05:37:29 -0400
I think you are talking about WITH clause usage in VDP. I'm talking about creating base view from query.
user
22-03-2018 12:01:50 -0400
Hi, I did the following and it worked for me. I was able to delegate the query to the database and return the resultset. Kindly do the following steps: 1. Open the base view you created using the "Create from query option" 2. Goto Options >Search Methods > wrapper source configuration options. 3. Now change the "Delegate SQL Sentence as Sub Query to "No" and save the view. 4. Now execute the view and you will be able to view the data. To know more about search methods refer to the section ["Query Capabilities"](https://community.denodo.com/docs/html/browse/6.0/vdp/administration/creating_views/advanced_configuration_of_views/query_capabilities#query-capabilities) of the Virtual DataPort Administration Guide. Hope this helps.
Denodo Team
12-04-2018 09:19:08 -0400
You must sign in to add an answer. If you do not have an account, you can register here