CTE not working

Toad Data Point & Toad Intelligence Central
1

Hello Im trying to import this query to toad but I got an error
Error:
"[Microsoft][ODBC Excel Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'."

SQL QUERY:

WITH DepartmentSalaries AS (
SELECT
department_id,
AVG(salary) AS avg_salary
FROM
employees
GROUP BY
department_id
)
SELECT
e.employee_id,
e.employee_name,
ds.avg_salary
FROM
employees e
JOIN
DepartmentSalaries ds
ON
e.department_id = ds.department_id;

2

I guess you mean Toad Data Point, not Toad for Oracle. Toad for Oracle doesn't use the ODBC Excel Driver. I will move your post to that forum.

3

yes please , thanks!

4

Hi,

The issue you’re encountering with CTEs is likely due to the limitations of the ODBC Excel driver, which doesn’t support Common Table Expressions (CTEs). To make your query compatible with the Excel ODBC driver, try rewriting it using subqueries instead of a CTE. Here’s an example:

SELECT
e.employee_id,
e.employee_name,
ds.avg_salary
FROM
employees e
JOIN (
SELECT
department_id,
AVG(salary) AS avg_salary
FROM
employees
GROUP BY
department_id
) ds
ON
e.department_id = ds.department_id;

Let me know if that works for you!