SSMS ALTER VIEW query repeatedly gets me "Invalid column name" when that is not the case

Question

Intellisense refresh is not working and there are no other views with the same name under different schemas to confuse the query. The original table does have all columns stated as invalid in the error.

Msg 207, Level 16, State 6, Procedure TableauSales, Line 1 [Batch Start Line 0] Invalid column name 'PurchaseDate'.

Msg 207, Level 16, State 6, Procedure TableauSales, Line 1 [Batch Start Line 0] Invalid column name 'UnitsSold'.

Msg 207, Level 16, State 6, Procedure TableauSales, Line 1 [Batch Start Line 0] Invalid column name 'GrossRevenue'.

Query:

ALTER VIEW [MemberGrowth].[TableauSales] AS
SELECT PurchDate,
PlatformDetail,
LicenseType,
DistributionMethod,
Product,
SUM(cur_GrossRevenue) as cur_GrossRevenue,
SUM(cur_UnitsSold) as cur_UnitsSold,
SUM(pre_GrossRevenue) as pre_GrossRevenue,
SUM(pre_UnitsSold) as pre_UnitsSold


FROM 
(SELECT * FROM 
(
(SELECT
DISTINCT cast(PurchaseDate As Date) AS PurchDate,
PlatformDetail, LicenseType, DistributionMethod,
Product,

 /*Add any segmentation you want above */

cast(PurchaseDate As Date) as pre_week,
DATEADD(DAY, -7, PurchaseDate) as week_before
FROM MemberGrowth.TableauSales)

UNION ALL

(SELECT
DISTINCT cast(PurchaseDate As Date) AS PurchDate, 
PlatformDetail, LicenseType, DistributionMethod,
Product,

/*Add any segmentation you want above */

DATEADD(DAY, -6, PurchaseDate) as pre_week,
DATEADD(DAY, -8, PurchaseDate) as week_before
FROM MemberGrowth.TableauSales)

UNION ALL
(SELECT
DISTINCT cast(PurchaseDate As Date) AS PurchDate,
PlatformDetail, LicenseType, DistributionMethod,
Product,
 /*Add any segmentation you want above */
DATEADD(DAY, -5, PurchaseDate) as pre_week,
DATEADD(DAY, -9, PurchaseDate) as week_before
FROM MemberGrowth.TableauSales)
UNION ALL

(SELECT
DISTINCT cast(PurchaseDate As Date) AS PurchDate,
PlatformDetail, LicenseType, DistributionMethod,
Product,
 /*Add any segmentation you want above */

DATEADD(DAY, -4, PurchaseDate) as pre_week,
DATEADD(DAY, -10, PurchaseDate) as week_before
FROM MemberGrowth.TableauSales)
UNION ALL

(SELECT
DISTINCT cast(PurchaseDate As Date) AS PurchDate, 
PlatformDetail, LicenseType, DistributionMethod,
Product,
/*Add any segmentation you want above */

DATEADD(DAY, -3, PurchaseDate) as pre_week,
DATEADD(DAY, -11, PurchaseDate) as week_before
FROM MemberGrowth.TableauSales)

UNION ALL

(SELECT
DISTINCT cast(PurchaseDate As Date) AS PurchDate,
PlatformDetail, LicenseType, DistributionMethod,
Product,
 /*Add any segmentation you want above */

DATEADD(DAY, -2, PurchaseDate) as pre_week,
DATEADD(DAY, -12, PurchaseDate) as week_before
FROM MemberGrowth.TableauSales)
UNION ALL
(SELECT
DISTINCT cast(PurchaseDate As Date) AS PurchDate,
PlatformDetail, LicenseType, DistributionMethod,
Product,
/*Add any segmentation you want above */


DATEADD(DAY, -1, PurchaseDate) as pre_week,
DATEADD(DAY, -13, PurchaseDate) as week_before
FROM MemberGrowth.TableauSales)
  ) a

LEFT JOIN

(SELECT PurchaseDate as cur_week,
 PlatformDetail as cur_PlatformDetail, 
 /*Add any segmentation you want above */

 GrossRevenue as cur_GrossRevenue,
 UnitsSold as cur_UnitsSold

 FROM MemberGrowth.TableauSales) b
ON a.pre_week = b.cur_week AND
 /*Join by any segmentation you are doing below */

 a.PlatformDetail = b.cur_PlatformDetail) c

JOIN

(SELECT PurchaseDate as sec_date,
 PlatformDetail as pre_PlatformDetail,

 /*Add any segmentation you want above */

 GrossRevenue as pre_GrossRevenue,
 UnitsSold as pre_UnitsSold
 FROM MemberGrowth.TableauSales) d
ON c.week_before = d.sec_date
/*Join by Any segmentation you are doing below */
AND c.cur_PlatformDetail = d.pre_PlatformDetail

/*groupby by Any segmentation you are doing below */

GROUP BY PurchDate, PlatformDetail, LicenseType, DistributionMethod, Product

Answer 1

I see the issue - you are trying to do the following:

ALTER VIEW [MemberGrowth].[TableauSales]

And the query for the view references the object:

FROM MemberGrowth.TableauSales

Look familiar?

You're trying to alter the view definition by using a query that references the view.

You might want to look at the existing view definition to see the actual underlying table it is referencing.