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»»

CTE and inner join Expand / Collapse
Author
Message
Posted Tuesday, January 11, 2011 5:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:46 AM
Points: 123, Visits: 513
i am using a CTE expression, as part of a script that copy data between diffrent db's.
in the middle of the script i have the following code :
;WITH
cteGetNewVSOldGroup (oldGroupId, newGroupId)
AS
(
select t1.oldGroupId,t2.id from #tblGroups t1 Inner join
[DB1].[dbo].Dyn_Group t2 on t1.name=t2.name
where t2.CompanyRef=@newCompanyRef and t2.UserRef=@newUserId
)
INSERT INTO [DB1].[dbo].[Dyn_Group2]
([GrandParent]
,[Parent]
,[Child])
select t2.newGroupId,t3.newGroupId,t4.newGroupId from #tblGroup2Group t1
inner join cteGetNewVSOldGroup as t2 on t1.GrandParent=t2.oldGroupId
inner join cteGetNewVSOldGroup as t3 on t1.Parent=t3.oldGroupId
inner join cteGetNewVSOldGroup as t4 on t1.Child=t4.oldGroupId

i get an error : Invalid object name 'cteGetNewVSOldGroup'.
any idea?
thanks
Peleg
Post #1045825
Posted Tuesday, January 11, 2011 7:39 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:34 PM
Points: 15,661, Visits: 28,051
It's because you're trying to run it across databases. The CTE created in the database you're in, not the database you're running against.

I'm unsure of how to resolve that. I'll have to do a bit of research.


----------------------------------------------------
"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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1045900
Posted Tuesday, January 11, 2011 8:15 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 5, 2014 1:31 PM
Points: 387, Visits: 586
Are you referencing cteGetNewVSOldGroup later in your script?
Post #1045926
Posted Tuesday, January 11, 2011 8:23 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:34 PM
Points: 15,661, Visits: 28,051
Maxim Picard (1/11/2011)
Are you referencing cteGetNewVSOldGroup later in your script?


That may be an issue as well, but if you try to hop a database and reference a CTE, you'll get that exact error.


----------------------------------------------------
"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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1045930
Posted Tuesday, January 11, 2011 8:30 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, May 5, 2014 1:31 PM
Points: 387, Visits: 586
Grant Fritchey (1/11/2011)
Maxim Picard (1/11/2011)
Are you referencing cteGetNewVSOldGroup later in your script?


That may be an issue as well, but if you try to hop a database and reference a CTE, you'll get that exact error.


Hi Grant,

I don't understand what you mean. How can you change database context after creating the CTE? I am trying to replicate his script with test tables and not getting the error.

Maybe I need another cofee :)

CREATE DATABASE DB1
CREATE DATABASE DB2
GO

USE DB1
GO

CREATE TABLE dbo.TableInDB1(
col1 CHAR(1)
)
GO

INSERT dbo.TableInDB1 VALUES ('1')
GO

--==== Switch to another context
USE DB2
GO

;WITH cteTest (col1) AS
(
SELECT
col1
FROM
DB1.dbo.TableInDB1

)
SELECT
*
FROM
cteTest
Post #1045935
Posted Tuesday, January 11, 2011 8:51 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:34 PM
Points: 15,661, Visits: 28,051
Well now I can't recreate it. Nuts. I should have kept that code I had that generated it.


----------------------------------------------------
"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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1045952
Posted Tuesday, January 11, 2011 9:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 3:50 PM
Points: 292, Visits: 1,623
It looks like the CTE is being refernced in more than one query:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=155076
Post #1045991
Posted Tuesday, January 11, 2011 12:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:46 AM
Points: 123, Visits: 513
"SSC Veteran" - what do you mean by that?
that you can't use the same cte more then once in one session?
Post #1046079
Posted Tuesday, January 11, 2011 12:26 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 3:34 PM
Points: 15,661, Visits: 28,051
peleg (1/11/2011)
"SSC Veteran" - what do you mean by that?
that you can't use the same cte more then once in one session?


Yep. A CTE can be referenced multiple times inside of the statement immediately following it, but it can't be referenced by multiple statements within the query.


----------------------------------------------------
"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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1046082
Posted Tuesday, January 11, 2011 12:26 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 10:32 AM
Points: 1,692, Visits: 3,368
The CTE "belongs" to the query, not the session.

IOW, if you want to persist it for the entire session you will not be able to use a CTE - you could use a table variable or temp table

jg
Post #1046083
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse