Eirikur Eiriksson (4/8/2014)
hisakimatama (4/8/2014)
Aha, so it doesn't :-). I've always declared CTEs with the column declarations, and by the wonderful problems of habit, it stuck as "the" way to do it. Well, I learned something that should make syntax a good bit clearer myself 😀I prefer it, makes the code more readable.
😎
That is just an opinion. There are times when it makes sense and times when it doesn't.
However, every column in a cte MUST be named. If you have a derived column of some sort if MUST have a name.
In the following you will see I have a column with the constant 'asdf' but the column has no name. This will not parse.
with MyCte as
(
select top 5 'asdf'
, name
from sys.objects
)
select * from MyCte;
But, simply add a column alias and it is fine.
with MyCte as
(
select top 5 'asdf' as MyColumn
, name
from sys.objects
)
select * from MyCte;
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/