Link to home
Start Free TrialLog in
Avatar of Fred
FredFlag for United States of America

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,ReviewDate)
            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
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

try this instead:

WITH RatingInfo (
	ReviewerName
	,EmailAddress
	,Rating
	,ReviewDate
	)
AS
	--We Define the CTE query
	(
	SELECT ReviewerName
		,EmailAddress
		,Rating
		,ReviewDate
	FROM Production.ProductReview
	)
	,CTE_RatingInfo
AS
	--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

Open in new window


or

WITH RatingInfo (
	ReviewerName
	,EmailAddress
	,Rating
	,ReviewDate
	)
AS
	--We Define the CTE query
	(
	SELECT ReviewerName
		,EmailAddress
		,Rating
		,ReviewDate
	FROM Production.ProductReview
	)
	,CTE_RatingInfo
AS
	--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 CTE_RatingInfo

Open in new window

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%';

Open in new window

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%';

Open in new window

Avatar of Fred

ASKER

Msg 102, Level 15, State 1, Line 104
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.
Avatar of Fred

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%'
Avatar of Fred

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%'
[..] 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.
Avatar of Fred

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
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial