SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CTE and inner join


CTE and inner join

Author
Message
peleg
peleg
SSC Veteran
SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)

Group: General Forum Members
Points: 293 Visits: 537
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
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55089 Visits: 32797
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Maxim Picard
Maxim Picard
SSC-Addicted
SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)

Group: General Forum Members
Points: 498 Visits: 594
Are you referencing cteGetNewVSOldGroup later in your script?
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55089 Visits: 32797
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Maxim Picard
Maxim Picard
SSC-Addicted
SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)SSC-Addicted (498 reputation)

Group: General Forum Members
Points: 498 Visits: 594
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 Smile

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

Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55089 Visits: 32797
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Lamprey13
Lamprey13
Say Hey Kid
Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)Say Hey Kid (702 reputation)

Group: General Forum Members
Points: 702 Visits: 1657
It looks like the CTE is being refernced in more than one query:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=155076
peleg
peleg
SSC Veteran
SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)SSC Veteran (293 reputation)

Group: General Forum Members
Points: 293 Visits: 537
"SSC Veteran" - what do you mean by that?
that you can't use the same cte more then once in one session?
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)SSC Guru (55K reputation)

Group: General Forum Members
Points: 55089 Visits: 32797
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
gmby
gmby
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2405 Visits: 3799
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search