SQL Common table expression defined but not used

Question

I got an error "Common table expression defined but not used." when using CTE in SQL query

This is my SQL Query

With EmployeeCount(DepartmentId, TotalEmployees)
as
(
 Select [Department_ID], COUNT(*) as TotalEmployees
 from Employee
 group by [Department_ID]
)

select GETDATE()
 Select Department_Name , TotalEmployees from Department
join EmployeeCount
on Department.ID = EmployeeCount.DepartmentId
order by TotalEmployees

 

Answer 1

CTE in SQL

• CTE stands for common table expression.
• A CTE is a temporary result set, that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement, that immediately follows the CTE.
• It's strongly recommended to use common table expressions rather than to use subqueries because common table expressions are more readable

Note: A CTE can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.

CTE Syntax

The syntax of a CTE in SQL Server:

WITH expression_name[(column_name ,..)]
AS
    (CTE_definition)
SQL_statement;

SQL Common table expression defined but not used

Regarding the error message, this error occurs because you have select GETDATE () between The CTE and select Statement.