Invalid Object Name When Referencing CTE Multiple Times

Question

I get the following error message when I run the code below and I can't see why. The code builds two chained CTEs and then runs SELECT statements against them.

Msg 208, Level 16, State 1, Line 76
Invalid object name 'FFCSource'

WITH FFCChildDemo AS
(   
    SELECT 
        us.UserSurveyID as Family_ID,
        LEFT(q.Question, 7) AS Child,
        CASE WHEN LTRIM(REPLACE(REPLACE(RIGHT(q.Question, LEN(q.Question) - 8), '-', ''), ':', '')) = 'Interests/hobbies/toy  $25, 1 item limit (please be specific)' 
                THEN 'Hobby/Interest: ' + REPLACE(REPLACE(COALESCE(usta.Answer, mc.Answer), CHAR(10), ' '), CHAR(13), ' ')  
                ELSE
                   CASE WHEN LTRIM(REPLACE(REPLACE(RIGHT(q.Question, LEN(q.Question) - 8), '-', ''), ':', '')) = 'Shirt Size' 
                           THEN 'Shirt - size: ' + REPLACE(REPLACE(COALESCE(usta.Answer, mc.Answer), CHAR(10), ' '), CHAR(13),' ') 
                           ELSE 
         case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'Pants Size' then 'Pants - size: ' + replace(replace(COALESCE(usta.Answer,mc.Answer),CHAR(10), ' '),char(13),' ') else
         case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'What is the highest priority item?' then 'Priority: ' + replace(replace(COALESCE(usta.Answer,mc.Answer),CHAR(10), ' '),char(13),' ') else
         replace(replace(COALESCE(usta.Answer,mc.Answer),CHAR(10), ' '),char(13),' ') end end end end as Answer
        ,case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'Interests/hobbies/toy  $25, 1 item limit (please be specific)' then 'Item1' else
         case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'Shirt Size' then 'Item1' else
         case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'Pants Size' then 'Item2' else
         case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'What is the highest priority item?' then 'Item1' else
         rtrim(ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':',''))) end end end end Question
    FROM FHS_SurveyApp_Survey s 
        JOIN FHS_SurveyApp_Question q ON q.SurveyID = s.SurveyID 
        JOIN FHS_SurveyApp_UserSurvey us ON us.SurveyID = s.SurveyID 
        LEFT JOIN FHS_SurveyApp_UserSurveyMultipleChoiceAnswer usmca ON usmca.QuestionID = q.QuestionID AND usmca.UserSurveyID = us.UserSurveyID 
        LEFT JOIN FHS_SurveyApp_MultipleChoice mc ON mc.MultipleChoiceID = usmca.MultipleChoiceID 
        LEFT JOIN FHS_SurveyApp_UserSurveyTextAnswer usta ON usta.QuestionID = q.QuestionID AND usta.UserSurveyID = us.UserSurveyID
    WHERE s.Name LIKE 'Freeman Family Christmas Application%'
        And q.QuestionID in (3221, 3224, 3227, 3228, 3229, 3230, 3231, 3364, 3222, 3225, 3233, 3234, 3235, 3368, 3370, 3371, 3330, 3332, 3333, 3334, 3337, 3338, 3339, 3365)
        AND us.TakenOn >= (GetDate() - 17)
    )
, FFCSource as
    (
    SELECT us.UserSurveyID as Family_ID
        ,left(q.Question,7) as Child
        ,case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'Interests/hobbies/toy  $25, 1 item limit (please be specific)' then left(q.Question,7) + ' - Hobby/Interest Tag' else
         case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'Shirt Size' then left(q.Question,7) + ' - Shirt/Pant Tag' else
         case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'Pants Size' then left(q.Question,7) + ' - Shirt/Pant Tag'  else
         case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'What is the highest priority item?' then left(q.Question,7) + ' - Priority Tag' else
         left(q.Question,7) + '??? Tag' end end end end as ChildTag

        ,case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'Interests/hobbies/toy  $25, 1 item limit (please be specific)' then 'Hobby/Interest: ' + replace(replace(COALESCE(usta.Answer,mc.Answer),CHAR(10), ' '),char(13),' ') else
         case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'Shirt Size' then 'Shirt - size: ' + replace(replace(COALESCE(usta.Answer,mc.Answer),CHAR(10), ' '),char(13),' ') else
         case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'Pants Size' then 'Pants - size: ' + replace(replace(COALESCE(usta.Answer,mc.Answer),CHAR(10), ' '),char(13),' ') else
         case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'What is the highest priority item?' then 'Priority: ' + replace(replace(COALESCE(usta.Answer,mc.Answer),CHAR(10), ' '),char(13),' ') else
         replace(replace(COALESCE(usta.Answer,mc.Answer),CHAR(10), ' '),char(13),' ') end end end end as Answer

        ,case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'Interests/hobbies/toy  $25, 1 item limit (please be specific)' then 'Item1' else
         case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'Shirt Size' then 'Item1' else
         case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'Pants Size' then 'Item2' else
         case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'What is the highest priority item?' then 'Item1' else
         rtrim(ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':',''))) end end end end Question
        --,q.Question Question
        --,us.TakenOn
        --,s.Name
        ,q.QuestionID          
    FROM FHS_SurveyApp_Survey s 
        JOIN FHS_SurveyApp_Question q ON q.SurveyID = s.SurveyID 
        JOIN FHS_SurveyApp_UserSurvey us ON us.SurveyID = s.SurveyID 
        LEFT JOIN FHS_SurveyApp_UserSurveyMultipleChoiceAnswer usmca ON usmca.QuestionID = q.QuestionID AND usmca.UserSurveyID = us.UserSurveyID 
        LEFT JOIN FHS_SurveyApp_MultipleChoice mc ON mc.MultipleChoiceID = usmca.MultipleChoiceID 
        LEFT JOIN FHS_SurveyApp_UserSurveyTextAnswer usta ON usta.QuestionID = q.QuestionID AND usta.UserSurveyID = us.UserSurveyID
    WHERE s.Name LIKE 'Freeman Family Christmas Application%'
        And q.QuestionID in (3238, 3240, 3267, 3340, 3342, 3344, 3345, 3347, 3350, 3353, 3355, 
                            3357, 3359, 3360, 3363, 3366, 3367, 3369, 3226, 3358, 3349, 3341, 
                            3237, 3351, 3343, 3239, 3361, 3352, 3241, 3362, 3354, 3346)
        AND us.TakenOn >= (GetDate() - 17)
    )
Select FFCChildDemoPivot.Family_ID, FFCChildDemoPivot.Child, FFCChildDemoPivot.[Age], FFCChildDemoPivot.[Gender], FFCChildDemoPivot.[Favorite color]
    , IndvOutput.ChildTag, IndvOutput.[Item1], IndvOutput.[Item2]
From FFCChildDemo
    PIVOT(Max(Answer) FOR Question IN ([Age], [Gender], [Favorite color])) AS FFCChildDemoPivot
    Left Join (
                Select Family_ID, Child, ChildTag, [Item1], [Item2]
                From FFCSource
                   PIVOT(Max(Answer) FOR Question IN ([Item1], [Item2])) AS FFC_IndvOutput
                   --PIVOT(Max(Answer) FOR Question IN ([Item1], [Item2])) AS FFC_IndvOutput
              ) IndvOutput on IndvOutput.Family_ID = FFCChildDemoPivot.Family_ID And IndvOutput.Child = FFCChildDemoPivot.Child



  Where (FFCChildDemoPivot.Age Is Not NULL And FFCChildDemoPivot.[Favorite color] is not NULL)
    Order by Family_ID, Child

    Select Family_ID, Child, ChildTag, [Item1], [Item2]
    From FFCSource
           PIVOT(Max(Answer) FOR Question IN ([Age], [Gender], [Favorite color], [Item1], [Item2])) AS FFC_IndvOutput
    Group by Family_ID, Child, ChildTag
    Order by Family_ID, ChildTag

score 1 · Answer 1 · answered Oct 23 '17 at 21:50

A CTE can only be referenced by the the SELECT statement that immediately follows it. In your case, the FFCSource CTE is referenced by two SQL statements that follow it. It will work for the first SELECT, and then throw this error when it reaches the second.

If you need to reference the CTE in multiple queries, then restructure, to write the results of FFCSource to a temp table, or table variable, and then reference the this.

Try something like this...

    SELECT us.UserSurveyID as Family_ID
        ,left(q.Question,7) as Child
        ,case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'Interests/hobbies/toy  $25, 1 item limit (please be specific)' then left(q.Question,7) + ' - Hobby/Interest Tag' else
         case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'Shirt Size' then left(q.Question,7) + ' - Shirt/Pant Tag' else
         case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'Pants Size' then left(q.Question,7) + ' - Shirt/Pant Tag'  else
         case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'What is the highest priority item?' then left(q.Question,7) + ' - Priority Tag' else
         left(q.Question,7) + '??? Tag' end end end end as ChildTag

        ,case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'Interests/hobbies/toy  $25, 1 item limit (please be specific)' then 'Hobby/Interest: ' + replace(replace(COALESCE(usta.Answer,mc.Answer),CHAR(10), ' '),char(13),' ') else
         case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'Shirt Size' then 'Shirt - size: ' + replace(replace(COALESCE(usta.Answer,mc.Answer),CHAR(10), ' '),char(13),' ') else
         case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'Pants Size' then 'Pants - size: ' + replace(replace(COALESCE(usta.Answer,mc.Answer),CHAR(10), ' '),char(13),' ') else
         case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'What is the highest priority item?' then 'Priority: ' + replace(replace(COALESCE(usta.Answer,mc.Answer),CHAR(10), ' '),char(13),' ') else
         replace(replace(COALESCE(usta.Answer,mc.Answer),CHAR(10), ' '),char(13),' ') end end end end as Answer

        ,case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'Interests/hobbies/toy  $25, 1 item limit (please be specific)' then 'Item1' else
         case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'Shirt Size' then 'Item1' else
         case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'Pants Size' then 'Item2' else
         case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'What is the highest priority item?' then 'Item1' else
         rtrim(ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':',''))) end end end end Question
        --,q.Question Question
        --,us.TakenOn
        --,s.Name
        ,q.QuestionID     
    INTO ##FFCSource     
    FROM FHS_SurveyApp_Survey s 
        JOIN FHS_SurveyApp_Question q ON q.SurveyID = s.SurveyID 
        JOIN FHS_SurveyApp_UserSurvey us ON us.SurveyID = s.SurveyID 
        LEFT JOIN FHS_SurveyApp_UserSurveyMultipleChoiceAnswer usmca ON usmca.QuestionID = q.QuestionID AND usmca.UserSurveyID = us.UserSurveyID 
        LEFT JOIN FHS_SurveyApp_MultipleChoice mc ON mc.MultipleChoiceID = usmca.MultipleChoiceID 
        LEFT JOIN FHS_SurveyApp_UserSurveyTextAnswer usta ON usta.QuestionID = q.QuestionID AND usta.UserSurveyID = us.UserSurveyID
    WHERE s.Name LIKE 'Freeman Family Christmas Application%'
        And q.QuestionID in (3238, 3240, 3267, 3340, 3342, 3344, 3345, 3347, 3350, 3353, 3355, 
                            3357, 3359, 3360, 3363, 3366, 3367, 3369, 3226, 3358, 3349, 3341, 
                            3237, 3351, 3343, 3239, 3361, 3352, 3241, 3362, 3354, 3346)
        AND us.TakenOn >= (GetDate() - 17)


WITH FFCChildDemo AS
(   
    SELECT 
        us.UserSurveyID as Family_ID,
        LEFT(q.Question, 7) AS Child,
        CASE WHEN LTRIM(REPLACE(REPLACE(RIGHT(q.Question, LEN(q.Question) - 8), '-', ''), ':', '')) = 'Interests/hobbies/toy  $25, 1 item limit (please be specific)' 
                THEN 'Hobby/Interest: ' + REPLACE(REPLACE(COALESCE(usta.Answer, mc.Answer), CHAR(10), ' '), CHAR(13), ' ')  
                ELSE
                   CASE WHEN LTRIM(REPLACE(REPLACE(RIGHT(q.Question, LEN(q.Question) - 8), '-', ''), ':', '')) = 'Shirt Size' 
                           THEN 'Shirt - size: ' + REPLACE(REPLACE(COALESCE(usta.Answer, mc.Answer), CHAR(10), ' '), CHAR(13),' ') 
                           ELSE 
         case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'Pants Size' then 'Pants - size: ' + replace(replace(COALESCE(usta.Answer,mc.Answer),CHAR(10), ' '),char(13),' ') else
         case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'What is the highest priority item?' then 'Priority: ' + replace(replace(COALESCE(usta.Answer,mc.Answer),CHAR(10), ' '),char(13),' ') else
         replace(replace(COALESCE(usta.Answer,mc.Answer),CHAR(10), ' '),char(13),' ') end end end end as Answer
        ,case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'Interests/hobbies/toy  $25, 1 item limit (please be specific)' then 'Item1' else
         case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'Shirt Size' then 'Item1' else
         case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'Pants Size' then 'Item2' else
         case when ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':','')) = 'What is the highest priority item?' then 'Item1' else
         rtrim(ltrim(replace(replace(right(q.Question,len(q.Question)-8),'-', ''),':',''))) end end end end Question
    FROM FHS_SurveyApp_Survey s 
        JOIN FHS_SurveyApp_Question q ON q.SurveyID = s.SurveyID 
        JOIN FHS_SurveyApp_UserSurvey us ON us.SurveyID = s.SurveyID 
        LEFT JOIN FHS_SurveyApp_UserSurveyMultipleChoiceAnswer usmca ON usmca.QuestionID = q.QuestionID AND usmca.UserSurveyID = us.UserSurveyID 
        LEFT JOIN FHS_SurveyApp_MultipleChoice mc ON mc.MultipleChoiceID = usmca.MultipleChoiceID 
        LEFT JOIN FHS_SurveyApp_UserSurveyTextAnswer usta ON usta.QuestionID = q.QuestionID AND usta.UserSurveyID = us.UserSurveyID
    WHERE s.Name LIKE 'Freeman Family Christmas Application%'
        And q.QuestionID in (3221, 3224, 3227, 3228, 3229, 3230, 3231, 3364, 3222, 3225, 3233, 3234, 3235, 3368, 3370, 3371, 3330, 3332, 3333, 3334, 3337, 3338, 3339, 3365)
        AND us.TakenOn >= (GetDate() - 17)
    )



Select FFCChildDemoPivot.Family_ID, FFCChildDemoPivot.Child, FFCChildDemoPivot.[Age], FFCChildDemoPivot.[Gender], FFCChildDemoPivot.[Favorite color]
    , IndvOutput.ChildTag, IndvOutput.[Item1], IndvOutput.[Item2]
From FFCChildDemo
    PIVOT(Max(Answer) FOR Question IN ([Age], [Gender], [Favorite color])) AS FFCChildDemoPivot
    Left Join (
                Select Family_ID, Child, ChildTag, [Item1], [Item2]
                From ##FFCSource FFCSource
                   PIVOT(Max(Answer) FOR Question IN ([Item1], [Item2])) AS FFC_IndvOutput
                   --PIVOT(Max(Answer) FOR Question IN ([Item1], [Item2])) AS FFC_IndvOutput
              ) IndvOutput on IndvOutput.Family_ID = FFCChildDemoPivot.Family_ID And IndvOutput.Child = FFCChildDemoPivot.Child



  Where (FFCChildDemoPivot.Age Is Not NULL And FFCChildDemoPivot.[Favorite color] is not NULL)
    Order by Family_ID, Child

    Select Family_ID, Child, ChildTag, [Item1], [Item2]
    From ##FFCSource FFCSource
           PIVOT(Max(Answer) FOR Question IN ([Age], [Gender], [Favorite color], [Item1], [Item2])) AS FFC_IndvOutput
    Group by Family_ID, Child, ChildTag
    Order by Family_ID, ChildTag

Invalid Object Name When Referencing CTE Multiple Times

1 Answers1