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 11:24 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:39 PM
Points: 245, Visits: 735
with UnloadDates as	select DISTINCT ShipmentID,
(select Min(starttime)
from tblInvoice I
where I.ShipmentID = O.shipmentID
and DataSent is null
) StartTime,
(select Max(Endtime)
from tblInvoice I
where I.ShipmentID = O.shipmentID
and DataSent is null
) EndTime
from tblInvoice O
where O.startTime IS NOT NULL
and O.EndTime IS NOT NULL
and O.DataSent is null

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'select'.


Appreciate the help


<><
Livin' down on the cube farm. Left, left, then a right.
Post #1559601
Posted Tuesday, April 8, 2014 11:30 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, October 17, 2014 2:56 PM
Points: 590, Visits: 6,810
Hrm. Seems like your CTE declaration is missing a few pieces; I believe it should be like so:

with UnloadDates(ShipmentId,StartTime,EndTime) as(
select DISTINCT ShipmentID,
(select Min(starttime)
from tblInvoice I
where I.ShipmentID = O.shipmentID
and DataSent is null
) StartTime,
(select Max(Endtime)
from tblInvoice I
where I.ShipmentID = O.shipmentID
and DataSent is null
) EndTime
from tblInvoice O
where O.startTime IS NOT NULL
and O.EndTime IS NOT NULL
and O.DataSent is null)

The CTE needs to have a column listing in the format of: CTEName(Column1,Column2,...). After the AS, you need a starting parenthesis, which you close at the end of your CTE query.

Post back if it's still giving you issues; I believe I caught the errors, but there might be something I overlooked.

EDIT: Doh, forgot the closing parenthesis.




-
Post #1559606
Posted Tuesday, April 8, 2014 11:41 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:18 AM
Points: 2,181, Visits: 5,908
hisakimatama (4/8/2014)

EDIT: Doh, forgot the closing parenthesis.

The parenthesis where the problem, a CTE does not require the columnar declaration.
Post #1559618
Posted Tuesday, April 8, 2014 11:51 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:39 AM
Points: 3,742, Visits: 8,390
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.



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1559623
Posted Tuesday, April 8, 2014 11:58 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:39 PM
Points: 245, Visits: 735
Arrgh. Sorry. I keep thinking those are input parameters, not output.

Another issue I had is that you can not just "compile" (execute) a CTE definition by itself without some follow on expressions.


<><
Livin' down on the cube farm. Left, left, then a right.
Post #1559628
Posted Tuesday, April 8, 2014 12:07 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:39 PM
Points: 245, Visits: 735
Luis Cazares (4/8/2014)
Since you're correcting this code, why don't we make it simpler and faster?
This way you read the table just once instead of trice. It's also a lot less code.

Oye. I didn't write it that way because I have a memory, evidently faulty, that that would not work. But it does. And since it does, I might not even need the CTE.

You guys (which includes gals) are the best!!!!


<><
Livin' down on the cube farm. Left, left, then a right.
Post #1559631
Posted Tuesday, April 8, 2014 12:18 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 5:30 AM
Points: 13,864, Visits: 28,260
Tobar (4/8/2014)
Arrgh. Sorry. I keep thinking those are input parameters, not output.

Another issue I had is that you can not just "compile" (execute) a CTE definition by itself without some follow on expressions.


Joke?

If not, that's because a CTE is just a query, a derived table, no different than if you did a SELECT FROM a SELECT FROM. The CTE only exists within the context of it's following statement.

If it was a joke, sorry for getting pedantic.


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1559638
Posted Tuesday, April 8, 2014 12:22 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:39 PM
Points: 245, Visits: 735
Alas ignorance, possibly just not thinking it through, but most likely ignorance.

<><
Livin' down on the cube farm. Left, left, then a right.
Post #1559640
Posted Tuesday, April 8, 2014 12:23 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, October 17, 2014 2:56 PM
Points: 590, Visits: 6,810
Eirikur Eiriksson (4/8/2014)
hisakimatama (4/8/2014)

EDIT: Doh, forgot the closing parenthesis.

The parenthesis where the problem, a CTE does not require the columnar declaration.


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




-
Post #1559641
Posted Tuesday, April 8, 2014 12:28 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:18 AM
Points: 2,181, Visits: 5,908
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.
Post #1559643
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse