question

MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 asked DanGuzman edited

Theory question on CTE

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:
83207-q1.png


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

Practice on CTE:
83188-q2.png

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


sql-server-transact-sql
q1.png (83.6 KiB)
q2.png (81.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DanGuzman avatar image
0 Votes"
DanGuzman answered DanGuzman edited

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MikhailFirsov-1277 avatar image
0 Votes"
MikhailFirsov-1277 answered MikhailFirsov-1277 edited

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.