From: tfs@dslextreme.com   
      
   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.   
      
   Tom   
      
   "tshad"  wrote in message   
   news:4c0vo.7021$qz.821@newsfe04.iad...   
   >I have a common table expression that runs VERY slow.   
   >   
   > The CTE query definition section takes about 2 seconds to run and has 99   
   > rows in it.   
   >   
   > But then I do a Select from it where I am looking for the latest date and   
   > it takes about 30 minutes to run.   
   >   
   > The query looks something like:   
   >   
   > ;WITH MyQuery   
   > AS   
   > (   
   > SELECT ...   
   > )   
   > SELECT  RetailerName 'Retailer'   
   > ,sQuestionText 'Question'   
   > ,sChoiceText 'Response Choice'   
   > -- ,COUNT(*)   
   > FROM 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)))   
   >   
   > If I do the @Status = 'ALL' (where all the rows are returned) takes about   
   > 3 seconds.  If I have @Status = 'ALL' (where only the group with the   
   > largest date is returned - should return 92) - it takes over 30 minutes to   
   > run.   
   >   
   > If I replace the CTE with a temporary table or temporary variable - it   
   > takes about 6 seconds.   
   >   
   > How can that be???   
   >   
   > I thought the CTE actually creates temp table internallly.   
   >   
   > The only change I make to the last SELECT statment is to change MyCTE to   
   > #table.   
   >   
   > I also tried to use a temporary variable and changed all the reference   
   > form #table to @table (as well as change the declaration of the table) and   
   > it works fine if I don't have the where clause, but the where clause:   
   >   
   > WHERE (@sRecordStatus = 'ALL') OR ((@sRecordStatus <> 'ALL') AND (dStart =   
   > (Select MAX(dStart)   
   >     from @tfs   
   >     WHERE  @tfs.Retailer = Retailer and   
   >         @tfs.StoreID = StoreID and   
   >         @tfs.QuestionID = QuestionID)))   
   >   
   > I get the error:   
   >   
   > Must declare the scalar variable "@tfs"   
   >   
   > Does that mean you can't use temporary variables in a sub query???   
   >   
   > Thanks,   
   >   
   > Tom   
   >   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)