Click here to monitor SSC
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16673 Visits: 17032
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 Moden's 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
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6837 Visits: 17774
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-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45443 Visits: 39944
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

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

Group: General Forum Members
Points: 6837 Visits: 17774
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-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24281 Visits: 37988
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
SSC Eights!
SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)SSC Eights! (825 reputation)

Group: General Forum Members
Points: 825 Visits: 8269
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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1136 Visits: 3229
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