SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Error with Join after CTE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

H2OCE
Starting Member

4 Posts

Posted - 10/31/2013 :  11:52:07  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3593 Posts

Posted - 10/31/2013 :  12:01:25  Show Profile  Reply with Quote
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?
Go to Top of Page

H2OCE
Starting Member

4 Posts

Posted - 10/31/2013 :  12:26:33  Show Profile  Reply with Quote
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.
Go to Top of Page

H2OCE
Starting Member

4 Posts

Posted - 10/31/2013 :  12:30:50  Show Profile  Reply with Quote
James,
Do you know how to automatically save results to a csv file in a query rather than manually saving results to csv?
Thanks.........
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3593 Posts

Posted - 10/31/2013 :  13:38:44  Show Profile  Reply with Quote
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
Go to Top of Page

H2OCE
Starting Member

4 Posts

Posted - 10/31/2013 :  14:10:41  Show Profile  Reply with Quote
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! :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000