SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


What is wrong with this syntax? Query will work, but CTE will not "compile"


What is wrong with this syntax? Query will work, but CTE will not "compile"

Author
Message
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25902 Visits: 17518
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 :-D

I prefer it, makes the code more readable.
Cool


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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Eirikur Eiriksson
Eirikur Eiriksson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14932 Visits: 18591
Sean Lange (4/8/2014)
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 :-D

I prefer it, makes the code more readable.
Cool


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;


Thanks Sean, I should have been more clear on that.
Cool
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85130 Visits: 41077
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 :-D

I prefer it, makes the code more readable.
Cool


I've found just the opposite to be true if there are a ton of columns being returned by the CTE. I'd just as soon write it as a good and proper encapsulated SELECT and let the defintion of the CTE be simple.

But, to each their own! I even change my ways when I'm working for a different company and they say how they want it done. :-P

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eirikur Eiriksson
Eirikur Eiriksson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14932 Visits: 18591
Jeff Moden (4/27/2014)
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 :-D

I prefer it, makes the code more readable.
Cool


I've found just the opposite to be true if there are a ton of columns being returned by the CTE. I'd just as soon write it as a good and proper encapsulated SELECT and let the defintion of the CTE be simple.

But, to each their own! I even change my ways when I'm working for a different company and they say how they want it done. :-P

Let me rephrase it, I prefer it, when it makes the code more readable:-P
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39034 Visits: 38513
Eirikur Eiriksson (4/27/2014)
Jeff Moden (4/27/2014)
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 :-D

I prefer it, makes the code more readable.
Cool


I've found just the opposite to be true if there are a ton of columns being returned by the CTE. I'd just as soon write it as a good and proper encapsulated SELECT and let the defintion of the CTE be simple.

But, to each their own! I even change my ways when I'm working for a different company and they say how they want it done. :-P

Let me rephrase it, I prefer it, when it makes the code more readable:-P


I usually make sure to name all the columns in the select list but there are times when I define the list of columns separately but not sure when or why. I have actually gotten away from it since working with Oracle for a year as the subquery refactoring clause doesn't support the definition of the column names upfront.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Greg Edwards-268690
Greg Edwards-268690
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1503 Visits: 8482
Luis Cazares (4/8/2014)
Since you're correcting this code, why don't we make it simpler and faster?

WITH UnloadDates AS(
SELECT ShipmentID,
MIN(starttime) StartTime,
MAX(Endtime) EndTime
FROM tblInvoice O
WHERE O.startTime IS NOT NULL
AND O.EndTime IS NOT NULL
AND O.DataSent is null
GROUP BY ShipmentID
)
SELECT ShipmentID,
StartTime,
EndTime
FROM UnloadDates


This way you read the table just once instead of trice. It's also a lot less code. :-)


Max and Min, are DISTINCT measures.
I think that was missed by the poster.
Looks like they started piece at a time, and lost sight of how it all really fits together.

They may be reading out of a history file, which has change by change history.
Possible there may be a current record file, where the dates are populated, and the DataSent IS NULL could be used.
Or a physical file, where there is a logical view already built for this.
Something for them to investigate.
Jason-299789
Jason-299789
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2067 Visits: 3232
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 :-D


Declaring the columns in a CTE is a simple way of Aliasing or overriding the Alias in the inner select, the downside is that it also prevents you from simply adding columns without also adding them to the Output List, which is a PITA in a development environment.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search