Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

What is wrong with this syntax? Query will work, but CTE will not "compile" Expand / Collapse
Author
Message
Posted Tuesday, April 8, 2014 12:48 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:05 PM
Points: 13,469, Visits: 12,325
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 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)
Post #1559660
Posted Tuesday, April 8, 2014 1:22 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 1,976, Visits: 5,142
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

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;


Thanks Sean, I should have been more clear on that.
Post #1559678
Posted Sunday, April 27, 2014 7:55 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:00 AM
Points: 37,099, Visits: 31,650
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.


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.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1565435
Posted Sunday, April 27, 2014 10:01 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 1,976, Visits: 5,142
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

I prefer it, makes the code more readable.


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.

Let me rephrase it, I prefer it, when it makes the code more readable
Post #1565448
Posted Sunday, April 27, 2014 10:06 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:07 PM
Points: 23,397, Visits: 32,243
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

I prefer it, makes the code more readable.


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.

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


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.



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)
Post #1565449
Posted Monday, April 28, 2014 6:24 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 11:25 AM
Points: 1,248, Visits: 6,692
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.

Post #1565547
Posted Thursday, May 1, 2014 2:27 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:09 AM
Points: 878, Visits: 2,395
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


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
Post #1566651
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse