当我运行下面的代码时,我收到以下错误消息,但我看不出为什么。该代码生成两个链接的 CTE,然后针对它们运行 SELECT 语句。
Msg 208,级别 16,状态 1,第 76
行 无效的对象名称"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
CTE 只能由紧跟在其后面的 SELECT 语句引用。在您的情况下,FFCSource CTE 由其后的两个 SQL 语句引用。它将适用于第一个 SELECT,然后在到达第二个时抛出此错误。
如果需要在多个查询中引用 CTE,请重构,将 FFCSource 的结果写入临时表或表变量,然后引用 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