Error with Join after CTE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Error with Join after CTE

Author

Topic

H2OCE
Starting Member

4 Posts

Posted - 2013-10-31 : 11:52:07

I am getting an error: Msg 208, Level 16, State 1, Line 353
Invalid object name 'SampleData_CTE'. I tried making another CTE around the (select, from, option statements)before the final join and I would get an error about incorrect syntax near OPTION(MaxRecursion 0). The DateTimeSTring prints out, then the error pops up.

declare @date datetime = '19900101'
declare @years int = 25 -- The number of years our data set contains data for
Declare @tributary varchar(20) ='Silver'
Declare @tributary2 varchar(20) = 'Silverx'

;

WITH SampleData_CTE (Tributary, RoundedHourDate, SampleDateTime, TSSmgpl, TPugpl, SRPugpl)
AS
-- Define the first CTE query.
(

SELECT
[Tributary]
,DATEADD(HOUR, datediff(HOUR, 0, dateadd(MINUTE, 30, [Sample Date & Time])),0) as RoundedHourDate
,[Sample Date & Time] as SampleDateTime
,[TSSmgpl]
,[TPmgpL]*1000 as TPugpl
,[SRPmgL]*1000 as SRPugpl
FROM [cob_waterquality].[dbo].[PhaseI$]
WHERE Tributary = @tributary or [Tributary] = @tributary2

UNION ALL

SELECT
[Tributary]
,DATEADD(HOUR, datediff(HOUR, 0, dateadd(MINUTE, 30, [Sample Date & Time])),0) as RoundedHourDate
,[Sample Date & Time] as SampleDateTime
,[TSSmgpl]
,[TPmgpL]*1000 AS TPugpl
,[SRPmgL]*1000 AS SRPugpl
FROM [cob_waterquality].[dbo].[PhaseII$]
WHERE Tributary = @tributary or [Tributary] = @tributary2

UNION ALL

SELECT
[site]
,DATEADD(HOUR, datediff(HOUR, 0, dateadd(MINUTE, 30, SampleDateTime)),0) as RoundedHourDate
,[month]
,[tss]as TSSmgpl
,[tp] as TPugpl
,[srp] as SRPugpl
FROM (
SELECT [code]
,[site]
,[month]
,[day]
,[year]
,[time]
,[tss]
,[tp]
,[srp]
,DATETIMEFROMPARTS([year], [month], [day], CAST([time]/100 AS int),Right([time],2),0,0 ) as SampleDateTime
FROM [cob_waterquality].[dbo].[creeks$]
) as newtable
WHERE [Site] = @tributary or [Site] = @tributary2

)
,

DateSeries_CTE(DateTimeString)
AS
(
select cast('1990-05-01' as datetime) DateTimeString
union all
select DATEADD(HOUR, 1, DateTimeString)
from DateSeries_CTE
where DATEADD(HOUR, 1, DateTimeString) < '2013-10-01'
)
Select DateTimeString
From DateSeries_CTE
OPTION(MAXRECURSION 0)

-- The Master Select statement
Select
DateTimeString
,[Tributary]
,RoundedHourDate
,ISNULL(round([TPugpl],3), 0 ) as TPugpl
FROM SampleData_CTE sampleData FULL JOIN DateSeries_CTE dateSeries
ON sampleData.RoundedHourDate = dateSeries.DateTimeString
ORDER BY DateTimeString

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-31 : 12:01:25

The "master select statement" has to be part of the query that starts with "WITH SampleData_CTE...". A query that involves a CTE is a single statement. You cannot have two final selects in it. Perhaps this is what you want?

.....
  WHERE     [Site] = @tributary
            OR [Site] = @tributary2
),      DateSeries_CTE ( DateTimeString )
          AS ( SELECT   CAST('1990-05-01' AS DATETIME) DateTimeString
               UNION ALL
               SELECT   DATEADD(HOUR, 1, DateTimeString)
               FROM     DateSeries_CTE
               WHERE    DATEADD(HOUR, 1, DateTimeString) < '2013-10-01'
             )
-- The Master Select statement
SELECT  DateTimeString ,
        [Tributary] ,
        RoundedHourDate ,
        ISNULL(ROUND([TPugpl], 3), 0) AS TPugpl
FROM    SampleData_CTE sampleData
        FULL JOIN DateSeries_CTE dateSeries ON sampleData.RoundedHourDate = dateSeries.DateTimeString
ORDER BY DateTimeString OPTION  ( MAXRECURSION 0 )

I have removed the part where you have

...Select DateTimeString 
From DateSeries_CTE
OPTION(MAXRECURSION 0)
...

What is the purpose of that?

H2OCE
Starting Member

4 Posts

Posted - 2013-10-31 : 12:26:33

OMG, thank you soooo much. That was it! I used a sample query from a website to create the datetimeseries string and it worked ok on its own but not when I stuck in the rest of my query. I don't know what the purpose of those deleted statements. I thought it was to keep it from going into an infinite loop. I am a total neophyte here. I just started learning SQL server last week so I don't understand all the parts and pieces yet. Again thanks so much for your help. I really appreciate it.

H2OCE
Starting Member

4 Posts

Posted - 2013-10-31 : 12:30:50

James,
Do you know how to automatically save results to a csv file in a query rather than manually saving results to csv?
Thanks.........

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-31 : 13:38:44

There are serveral ways to save the results to a file. Couple of them are described here: http://blog.sqlauthority.com/2013/09/12/sql-server-automatically-store-results-of-query-to-file-with-sqlcmd/

In SSMS, you can select Query -> Results to -> Results To File to save the query results to a file. (I am not sure if it is csv or fixed width though).

You can also use BCP - there are examples towards the end of this page: http://technet.microsoft.com/en-us/library/ms162802.aspx

Another option would be to use Import/Export wizard (or SSIS). To use Import/Export wizard, in SSMS, right click on the database name and select Tasks -> Export Data

H2OCE
Starting Member

4 Posts

Posted - 2013-10-31 : 14:10:41

Thanks. I have exported the results manually but wanted to know if I can do this within a query. I will look at the links you sent. Thanks again for your help. You've made my day! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources