Theory question on CTE

Mikhail Firsov 1,756 Reputation points


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,

No comments
{count} votes

Accepted answer
  1. Dan Guzman 7,121 Reputation points

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

    WITH CTE_EmDep (Employee, DepartmentName)  
     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.

1 additional answer

Sort by: Most helpful
  1. Mikhail Firsov 1,756 Reputation points

    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!