Fred
asked on
Created CTE shows Invalid Object name
Am creating a CTE table RatingInfo, I am getting this error invalid error
(2 rows affected)
Msg 208, Level 16, State 1, Line 100
Invalid object name 'RatingInfo'.
--- CREATING A CTE STRUCTURE
WITH RatingInfo (ReviewerName,EmailAddress ,Rating,Re viewDate)
AS
--We Define the CTE query
(
SELECT ReviewerName,
EmailAddress,
Rating,
ReviewDate
FROM Production.ProductReview
)
--Define the Outer query referencing CTE_RatingInfo
( SELECT ReviewerName,
EmailAddress,
Rating,
ReviewDate
FROM RatingInfo
WHERE Rating BETWEEN 2 AND 5
AND EmailAddress like 'J%'
)
SELECT * FROM RatingInfo
(2 rows affected)
Msg 208, Level 16, State 1, Line 100
Invalid object name 'RatingInfo'.
--- CREATING A CTE STRUCTURE
WITH RatingInfo (ReviewerName,EmailAddress
AS
--We Define the CTE query
(
SELECT ReviewerName,
EmailAddress,
Rating,
ReviewDate
FROM Production.ProductReview
)
--Define the Outer query referencing CTE_RatingInfo
( SELECT ReviewerName,
EmailAddress,
Rating,
ReviewDate
FROM RatingInfo
WHERE Rating BETWEEN 2 AND 5
AND EmailAddress like 'J%'
)
SELECT * FROM RatingInfo
E.g.
WITH RatingInfo
AS ( SELECT PR.ReviewerName ,
PR.EmailAddress ,
PR.Rating ,
PR.ReviewDate
FROM Production.ProductReview PR )
SELECT RI.ReviewerName ,
RI.EmailAddress ,
RI.Rating ,
RI.ReviewDate
FROM RatingInfo RI
WHERE RI.Rating
BETWEEN 2 AND 5
AND RI.EmailAddress LIKE 'J%';
But this sample is simple, that you don't need a CTE:SELECT PR.ReviewerName ,
PR.EmailAddress ,
PR.Rating ,
PR.ReviewDate
FROM Production.ProductReview PR
WHERE PR.Rating
BETWEEN 2 AND 5
AND PR.EmailAddress LIKE 'J%';
ASKER
Msg 102, Level 15, State 1, Line 104
Incorrect syntax near ')'.
I Tried Ryan Chong option and got the above error
Incorrect syntax near ')'.
I Tried Ryan Chong option and got the above error
As none of his solutions has more then 40 line, it is for sure either a copy'n'paste error or you have an error in the part of your script before the place you've inserted the CTE part. Cause the CTE's as posted are syntactically correct.
ASKER
ste5an, we are to create a cte from a table, why did you use PR.ReviewerName using PR ? and RI.ReviewerName RI ?
SELECT ReviewerName
EmailAddress,
Rating,
ReviewDate
FROM Production.ProductReview
WHERE Rating BETWEEN 2 AND 5
AND EmailAddress like 'J%'
SELECT ReviewerName
EmailAddress,
Rating,
ReviewDate
FROM Production.ProductReview
WHERE Rating BETWEEN 2 AND 5
AND EmailAddress like 'J%'
ASKER
Ok let me try again, Its show line 102, I am running other queries too. I am trying to create a temp table,CTE and table variable using this table SELECT ReviewerName
EmailAddress,
Rating,
ReviewDate
FROM Production.ProductReview
WHERE Rating BETWEEN 2 AND 5
AND EmailAddress like 'J%'
EmailAddress,
Rating,
ReviewDate
FROM Production.ProductReview
WHERE Rating BETWEEN 2 AND 5
AND EmailAddress like 'J%'
[..] why did you use PR.ReviewerName using PR ? and RI.ReviewerName RI ?Using table alias names makes reading queries easier.
Just open a new query window in SSMS. Then run the given possible solutions from above. Do they run? Then it's an error in your script, which you haven't shown use till now. Otherwise, it is either an typo or some object name conflicts like wrong schema or database.
ASKER
Thanks I opened a new query window and used the First solution, it retrieved the results needed.
name Rating
John Smith john@fourthcoffee.com 5 2013-09-18 00:00:00.000
Jill jill@margiestravel.com 2 2013-11-15 00:00:00.000
if I run SELECT * FROM CTE_RatingInfo by itself then should it not store the same results ? It returns this error
Msg 208, Level 16, State 1, Line 34
Invalid object name 'CTE_RatingInfo'.
Does this mean that its been stored to the duration of the execution scope ? Just asking
name Rating
John Smith john@fourthcoffee.com 5 2013-09-18 00:00:00.000
Jill jill@margiestravel.com 2 2013-11-15 00:00:00.000
if I run SELECT * FROM CTE_RatingInfo by itself then should it not store the same results ? It returns this error
Msg 208, Level 16, State 1, Line 34
Invalid object name 'CTE_RatingInfo'.
Does this mean that its been stored to the duration of the execution scope ? Just asking
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window
or
Open in new window