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

The native intellsense is pretty craptastic. You may have to close SSMS entirely when the refresh local cache doesn't work. — Sean Lange
We would need to see the query and know the structure of the tables. Intellisense not refreshing won't cause the error. You can force it to refresh with CTRL + SHIFT + R, but that won't fix the error. — Siyual
Or if there is an actual error that has nothing at all to do with intellisense. Is it just the red squiggly in the editor or an actual exception when you try to execute the alter script? — Sean Lange
The error is with the column name which doesn't exist from a table. — Ferdinand Gaspar
If the server gives you an error on executing the ALTER VIEW command, the server is right, regardless of what SMSS shows or does not show. Keep that in mind as you try to diagnose things -- eliminate the truly impossible, not the merely unlikely. — Jeroen Mostert

Siyual Siyual · Accepted Answer · 2017-08-17T21:01:50

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.

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

1 Answers