Theory question on CTE

Question

Hello!

Theory on Derived Tables: "~ ...when creating DTs you can define INLINE or EXTERNAL column aliases".

Practice on Derived Tables: I prefer EXTERNAL column aliases - and it works:

Theory on CTE: "~ define either INLINE or EXTERNAL column aliases".

Practice on CTE:

I failed to define the external column alias in CTE (I tried in various ways) and did not find any example on the matter either.
How can I "define external column aliases" in CTE?

Thank you in advance,
Michael

Answer 1

With a CTE, specify the external column aliases after the CTE name declaration per the syntax documentation:

WITH CTE_EmDep (Employee, DepartmentName)  
AS  
(  
 SELECT BusinessEntityID, Name  
 FROM HumanResources.EmployeeDepartmentHistory  
 INNER JOIN HumanResources.Department ON HumanResources.EmployeeDepartmentHistory.DepartmentID = HumanResources.Department.DepartmentID  
)  
SELECT Employee, DepartmentName  
FROM CTE_EmDep;  

Instead of images, please post code as text formatted with the Code Sample button going forward. Than will help us better help you.

Answer 2

Oh, I did not think the "column_name - Specifies a column name in the common table expression." means "external column aliases" ( I thought they must be defined after AS like in the DT).

Thank you very much!

Regards,
Michael