Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


CTE and inner join


CTE and inner join

Author
Message
peleg
peleg
SSC-Enthusiastic
SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)

Group: General Forum Members
Points: 131 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
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17592 Visits: 32265
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
Old Hand
Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)

Group: General Forum Members
Points: 394 Visits: 594
Are you referencing cteGetNewVSOldGroup later in your script?
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17592 Visits: 32265
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
Old Hand
Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)Old Hand (394 reputation)

Group: General Forum Members
Points: 394 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
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17592 Visits: 32265
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
Old Hand
Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)Old Hand (304 reputation)

Group: General Forum Members
Points: 304 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-Enthusiastic
SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)SSC-Enthusiastic (131 reputation)

Group: General Forum Members
Points: 131 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
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17592 Visits: 32265
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.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2288 Visits: 3765
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