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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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

3873 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

3873 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  
 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.05 seconds. Powered By: Snitz Forums 2000