SQLTeam.com | Weblogs | Forums

Invalid column name


#1

Hi,

I am getting the above error and cannot understand why, can anyone help please.

Table definition:
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
Incode varchar no 4 no no no Latin1_General_CI_AS
Eastings int no 4 10 0 yes (n/a) (n/a) NULL
Northings int no 4 10 0 yes (n/a) (n/a) NULL
Latitude decimal no 9 18 8 yes (n/a) (n/a) NULL
Longitude decimal no 9 18 8 yes (n/a) (n/a) NULL
Town varchar no 50 yes no yes Latin1_General_CI_AS
Region varchar no 50 yes no yes Latin1_General_CI_AS
CountryCode varchar no 3 yes no yes Latin1_General_CI_AS
CountryName varchar no 50 yes no yes Latin1_General_CI_AS
EnglandRegion varchar no 2 yes no yes Latin1_General_CI_AS

I am using the following case statement:

SUM(CASE When CountryName = 'England' and EnglandRegion ='NE' Then 1 Else 0 End) As NEast,
SUM(CASE When CountryName = 'England' and EnglandRegion ='NW' Then 1 Else 0 End) As NWest,
SUM(CASE When CountryName = 'England' and EnglandRegion ='EM' Then 1 Else 0 End) As EMidlands,
SUM(CASE When CountryName = 'England' and EnglandRegion ='WM' Then 1 Else 0 End) As WMidlands,
SUM(CASE When CountryName = 'England' and EnglandRegion ='SE' Then 1 Else 0 End) As SEast,
SUM(CASE When CountryName = 'England' and EnglandRegion ='SW' Then 1 Else 0 End) As SWest,
SUM(CASE When CountryName = 'England' and EnglandRegion IS NULL Then 1 Else 0 End) As GLondon


#2

what is the full error message ?

Is that the complete query ?


#3

Full query here:
SELECT GenderIsMale,
Count(GenderIsMale) as Sex,
SUM(CASE When Age Between 18 and 25 Then 1 Else 0 End) As Age1825,
SUM(CASE When Age Between 26 and 35 Then 1 Else 0 End) As Age2635,
SUM(CASE When Age Between 36 and 50 Then 1 Else 0 End) As Age3650,
SUM(CASE When Age Between 51 and 65 Then 1 Else 0 End) As Age5165,
SUM(CASE When Age >65 Then 1 Else 0 End) As Age65plus,
SUM(CASE When CountryName = 'Scotland' Then 1 Else 0 End) As Scotland,
SUM(CASE When CountryName = 'Wales' Then 1 Else 0 End) As Wales,
SUM(CASE When CountryName = 'Northern Ireland' Then 1 Else 0 End) As NIreland,
SUM(CASE When CountryName = 'England' and EnglandRegion ='NE' Then 1 Else 0 End) As NEast,
SUM(CASE When CountryName = 'England' and EnglandRegion ='NW' Then 1 Else 0 End) As NWest,
SUM(CASE When CountryName = 'England' and EnglandRegion ='EM' Then 1 Else 0 End) As EMidlands,
SUM(CASE When CountryName = 'England' and EnglandRegion ='WM' Then 1 Else 0 End) As WMidlands,
SUM(CASE When CountryName = 'England' and EnglandRegion ='SE' Then 1 Else 0 End) As SEast,
SUM(CASE When CountryName = 'England' and EnglandRegion ='SW' Then 1 Else 0 End) As SWest,
SUM(CASE When CountryName = 'England' and EnglandRegion IS NULL Then 1 Else 0 End) As GLondon

FROM (
SELECT u.PKID,
u.EmailAddress,
u.Title,
u.FirstName,
u.LastName,
ud.Address1,
ud.Address2,
ud.Address3,
ud.PostTown,
ud.County,
CountryName,
ud.PostCode,
DATEDIFF(hour,ud.DateOfBirth,GETDATE())/8766 AS Age,
reg.RegistrationDateTime,
ud.GenderIsMale,
--ROW_NUMBER() OVER (PARTITION BY ud.Address1, ud.Address2, ud.PostTown, ud.County, ud.PostCode ORDER BY u.PKID DESC) AS DedupeNumberPostalAddress,
ROW_NUMBER() OVER (PARTITION BY Telephone.TelephoneNumber ORDER BY u.PKID DESC) AS DedupeNumberTelephone
FROM PrizePunch..DataSalesUser
INNER JOIN PrizePunch.dbo.PrizePunchUser u WITH(NOLOCK)
ON u.PKID = DataSalesUser.UserID
INNER JOIN PrizePunch.dbo.PrizePunchUserDetail ud WITH(NOLOCK)
ON ud.UserID = u.PKID
INNER JOIN PrizePunch.dbo.PrizePunchUserRegistration reg WITH(NOLOCK)
ON reg.UserID = u.PKID
INNER JOIN PrizePunch..UserEmailSubscription WITH(NOLOCK)
ON UserEmailSubscription.UserID = u.PKID
AND UserEmailSubscription.EmailSubscriptionID = 2 -- Third party email subscription
INNER JOIN (
SELECT TelephoneNumber, UserID
FROM (
SELECT TelephoneNumber, UserID, RANK() OVER(PARTITION BY UserID ORDER BY UpdatedDateTime DESC) AS [Recency]
FROM PrizePunch..PrizePunchUserTelephone
WHERE Deleted = 0
AND CleanseResult = 1
AND ValidationStatusID = 2
--AND TelephoneTypeID = 2 --2=Mobile Number

		  ) sub
		  WHERE Recency = 1
		  GROUP BY TelephoneNumber, UserID
		  ) telephone
			ON telephone.UserID = u.PKID
	INNER JOIN UserListTransfer..PostcodeInfo 
		 ON ud.PostCode LIKE PostcodeInfo.Incode + '%'
	
WHERE 
u.Unsubscribed = 0
AND ISNULL(reg.ThirdPartyOptOut, 0) = 0

-- Cleansing
AND ud.Address1 != 'Address'
AND ud.Address1 != '10 DOWNING STREET'
AND OptInIP NOT IN ('103.4.16.137', '178.32.240.210', '103.4.18.71', '178.32.240.205', '185.7.149.234')

-- UK
AND u.MemberbaseID = 1

-- With name
AND u.NameCleanseResult = 1

-- With address
AND LEN(ud.Address1) > 1
AND LEN(ud.Postcode) > 1
AND LEN(ud.PostTown) > 1


) data

WHERE DedupeNumberTelephone = 1

GROUP BY GenderIsMale

Full error message:
Msg 207, Level 16, State 1, Line 15
Invalid column name 'EnglandRegion'.
Msg 207, Level 16, State 1, Line 16
Invalid column name 'EnglandRegion'.
Msg 207, Level 16, State 1, Line 17
Invalid column name 'EnglandRegion'.
Msg 207, Level 16, State 1, Line 18
Invalid column name 'EnglandRegion'.
Msg 207, Level 16, State 1, Line 19
Invalid column name 'EnglandRegion'.
Msg 207, Level 16, State 1, Line 20
Invalid column name 'EnglandRegion'.
Msg 207, Level 16, State 1, Line 21
Invalid column name 'EnglandRegion'.


#4

the column name "EnglandRegion" does not exists in your inner query. You need to add to that


#5

Hi,

EnglandRegion column is missing in your select query. Please add the column and try it.


#6

Of course, thanks for that.