December 30, 2009 at 8:09 pm
Hi,
I'm using CTEs. But if i query those in later part of the procedure i'm getting errors like invalid object name. for example in the following query
WITH Parts
AS
(
SELECT * FROM TINVENTORY WHERE Partno=320
)
, Trans as
(
SELECT TOP 10 * FROM TBRO WHERE ID =460
)
, TCust as
(
select top 10 * from tcust WHERE NAME LIKE '%SA%'
)
select * from parts
select * from trans
select * from tcust
i get error Msg 208, Level 16, State 1, Line 22
Invalid object name 'trans'.
why i'm getting these errors and how to clear it.
regards
Anamika
December 30, 2009 at 9:36 pm
hi,
Hope the following code helps u.....
the with statement can be given with only single select clause as below
WITH Parts
AS
(
SELECT * FROM TINVENTORY WHERE Partno=320
)
, Trans as
(
SELECT TOP 10 * FROM TBRO WHERE ID =460
)
, TCust as
(
select top 10 * from tcust WHERE NAME LIKE '%SA%'
)
select * from Parts,Trans , TCust
December 30, 2009 at 10:27 pm
Actually my question is can't I use the CTE in further part of the procedure. I found the answer is 'no'. before mentioning the CTE in query we have to redefine it again. WITH Parts
AS
(
SELECT * FROM TINVENTORY WHERE Partno=320
)
select * from parts
--some queries
.
.
.
WITH Trans as
(
SELECT TOP 10 * FROM TBRO WHERE ID =460
)
select * from trans
.
.
.
WITH TCust as
(
select top 10 * from tcust WHERE NAME LIKE '%SA%'
)
select * from tcust
If I want to refer the CTE in one more place before referring i should redefine it again.
-Anamika
December 31, 2009 at 5:29 am
Anamika (12/30/2009)
Actually my question is can't I use the CTE in further part of the procedure. I found the answer is 'no'. before mentioning the CTE in query we have to redefine it again.WITH PartsAS
(
SELECT * FROM TINVENTORY WHERE Partno=320
)
select * from parts
--some queries
.
.
.
WITH Trans as
(
SELECT TOP 10 * FROM TBRO WHERE ID =460
)
select * from trans
.
.
.
WITH TCust as
(
select top 10 * from tcust WHERE NAME LIKE '%SA%'
)
select * from tcust
If I want to refer the CTE in one more place before referring i should redefine it again.
-Anamika
... or you could store the results in a temp table to use it later int the proc either using SELECT INTO or INSERT INTO SELECT (when table already exists).
December 31, 2009 at 9:40 pm
Lutz is spot on... calculate the answers just once and store them in a temp table.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 1, 2010 at 9:46 am
just to reiterate, you can only use the cte in the following line after the definition so if you define three cte's you must use them all in the following select.
January 1, 2010 at 9:58 am
Just so it is clear and to reiterate once again -
Store the results of the cte in a temp table if they are needed for later consumption in the proc than the immediate next statement. It is not an uncommon thing to use that kind of method.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 1, 2010 at 10:47 am
Heh... Ok... since everyone is trying to be "clear", let's clear up a couple of other things about CTE's. CTE's work just like views do... if you call a CTE twice in the FROM clause of a given query, it will cause the CTE to run twice which can be devastating for performance especially if the two calls reference each other. I'll admit that properly used CTE's form a wonderful flow of "top down/very easy to read" form of code but if you need to use a CTE more than once even within the same query, then Jason is absolutely spot on... create a Temp Table using SELECT/INTO instead.
Also, just like cursors, recursive CTE's are RBAR (as all forms of recursion are) and should be used very sparingly and with great caution. Although very convenient, even using them for resolving hierarchies isn't the best use because people use them to calculate the same path over and over instead of just once and storing the result for future use. There are better ways. 😉
Also remember that the definition of high performance "Set Based" code should not be confused with "doing it all in a single query" which may include CTE's.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 1, 2010 at 7:15 pm
Jeff Moden (1/1/2010)
... Also remember that the definition of high performance "Set Based" code should not be confused with "doing it all in a single query" which may include CTE's.
[SET SARCASM ON]
Do you think there are people out there thinking that way?
[SET SARCASM OFF]
:-D:-P:-D
January 2, 2010 at 11:15 pm
lmu92 (1/1/2010)
Jeff Moden (1/1/2010)
... Also remember that the definition of high performance "Set Based" code should not be confused with "doing it all in a single query" which may include CTE's.[SET SARCASM ON]
Do you think there are people out there thinking that way?
[SET SARCASM OFF]
:-D:-P:-D
😉
--Jeff Moden
Change is inevitable... Change for the better is not.
January 4, 2010 at 1:32 am
This was removed by the editor as SPAM
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply