From: hugo@perFact.REMOVETHIS.info.INVALID   
      
   On Tue, 19 Oct 2010 17:54:22 -0700, tshad wrote:   
      
   >I had looked at CTE as just another way to write a derived table (when not   
   >dealing with recursion).   
   >   
   >I wanted to see if when I unwound the procedure if it would run the same.  I   
   >tried to do the following but I get an error on MyCTE.   
   >   
   >SELECT  RetailerName 'Retailer'   
   > ,sQuestionText 'Question'   
   > ,sChoiceText 'Response Choice'   
   >-- ,COUNT(*)   
   >FROM (   
   >SELECT ...   
   >) MyCTE   
   >WHERE (@Status = 'ALL') OR ((@Status <> 'ALL') AND (dStart = (Select   
   >MAX(dStart)   
   >     from MyCTE   
   >     WHERE  MyCTE.RetailerName = RetailerName and   
   >         MyCTE.StoreID = StoreID and   
   >         MyCTE.nQuestionID = nQuestionID)))   
   >   
   >The error I get is Invalid object name: MyCTE.   
   >   
   >I thought you could refer to the derived table in my where clause but   
   >apparently not.   
      
   You can use the alias of the derived table as a qualifier for column   
   names, but not as a table in the FROM clause of a subquery. Just as you   
   can't write   
     FROM SomeTable AS a   
     WHERE xyz = (SELECT ... FROM a WHERE ...)   
   but have to repeat the table name there.   
      
   This is one of the reasons why CTEs were invented, to enable you to   
   refer to the same subquery multiple times.   
      
   --   
   Hugo Kornelis, SQL Server MVP   
   My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)