Msg 207, Level 16, State 1 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Msg 207, Level 16, State 1

Author

Topic

lueylump
Starting Member

25 Posts

Posted - 2011-04-26 : 12:44:51

I am receiving an Invalid column name message on the "Inf_Name" column of a Temporary Table (see below):

Msg 207, Level 16, State 1, Line 88
Invalid column name 'Inf_Name'.
Msg 207, Level 16, State 1, Line 266
Invalid column name 'Inf_Name'.
Msg 207, Level 16, State 1, Line 263
Invalid column name 'Inf_Name'.
Msg 207, Level 16, State 1, Line 520
Invalid column name 'Inf_Name'.

I renamed the column multiple times but it still gives the same message. The following is the code (inapplicable steps were removed because procedure is so large, and I prefixed each offending line with "==>"):


--drop Table #Mkt_Activity_Summary

Go 

Declare @Yr1_Start_Date VARCHAR(30),
		@Yr2_Start_Date VARCHAR(30),
		@Yr3_Start_Date VARCHAR(30),
		@Yr4_Start_Date VARCHAR(30),
		@Ind_Flag char(1),
		@Org_Flag char(1),
		@Found_Flag char(1)

Set @Yr1_Start_Date = '2009-01-01 00:00:00.000'
Set @Yr2_Start_Date = '2010-01-01 00:00:00.000'
Set @Yr3_Start_Date = '2011-01-01 00:00:00.000'
Set @Yr4_Start_Date = '2012-01-01 00:00:00.000'
Set @Ind_Flag = 'Y' /* 'Y' = Yes, 'N' = No for 'Invividual' Selection Criteria */
Set @Org_Flag = 'Y' /* 'Y' = Yes, 'N' = No for 'Organization (Church)' Selection Criteria */
Set @Found_Flag = 'Y' /* 'Y' = Yes, 'N' = No for 'Foundation' Selection Criteria */

CREATE TABLE #Mkt_Activity_Summary
(
Group_Cd bigint,
Org_Name varchar(100),
Inf_Name varchar(100),
Soft_Group_Cd bigint,
Soft_Org_Name varchar(100),
Group_Type varchar(100), 
Mkt_Activity_ID varchar(100), 
Solicitor_Nm varchar(100),
Mkt_Activity varchar(100), 
Org_Addr_1 varchar (100),
Org_Addr_2 varchar (100),
Org_City varchar(100), 
Org_State varchar(2),
Org_Zip varchar(10),
Prev_Yr_CC money,
Curr_Yr_CC money,
Prev_Yr_DF money,
Curr_Yr_DF money,
Prev_Yr_Gifts bigint,
Prev_Yr_Giving money,
Curr_Yr_Gifts bigint,
Curr_Yr_Giving money,
Giving_Goal varchar(100),
Elim_Connection varchar(100),
Cap_Range varchar(100),
Lvl_Ngage varchar(100),
Disab_Aware varchar(100),
Giving_Status varchar(100),
Jan_Note varchar(1000),
Jan_P_Giving money,
Jan_Giving money,
Feb_Note varchar(1000),
Feb_P_Giving money,
Feb_Giving money,
Mar_Note varchar(1000),
Mar_P_Giving money,
Mar_Giving money,
Apr_Note varchar(1000),
Apr_P_Giving money,
Apr_Giving money,
May_Note varchar(1000),
May_P_Giving money,
May_Giving money,
Jun_Note varchar(1000),
Jun_P_Giving money,
Jun_Giving money,
Jul_Note varchar(1000),
Jul_P_Giving money,
Jul_Giving money,
Aug_Note varchar(1000),
Aug_P_Giving money,
Aug_Giving money,
Sep_Note varchar(1000),
Sep_P_Giving money,
Sep_Giving money,
Oct_Note varchar(1000),
Oct_P_Giving money,
Oct_Giving money,
Nov_Note varchar(1000),
Nov_P_Giving money,
Nov_Giving money,
Dec_Note varchar(1000),
Dec_P_Giving money,
Dec_Giving money,
Group_ID varchar(100),
Analysis_Fl char(1)
)

==> INSERT INTO #Mkt_Activity_Summary (Group_Cd, Org_Name, Inf_Name, Soft_Group_Cd, Soft_Org_Name, Group_Type, 
Mkt_Activity_ID, Solicitor_Nm, Mkt_Activity, Org_Addr_1, Org_Addr_2, Org_City, Org_State, Org_Zip, Prev_Yr_DF, 
Curr_Yr_DF, Prev_Yr_CC, Curr_Yr_CC, Prev_Yr_Gifts, Prev_Yr_Giving, Curr_Yr_Gifts, Curr_Yr_Giving, Giving_Goal, 
Elim_Connection, Cap_Range, Lvl_Ngage, Disab_Aware, Giving_Status, Jan_Note, Jan_P_Giving, Jan_Giving, Feb_Note, 
Feb_P_Giving, Feb_Giving, Mar_Note, Mar_P_Giving, Mar_Giving, Apr_Note, Apr_P_Giving, Apr_Giving, May_Note, 
May_P_Giving, May_Giving, Jun_Note, Jun_P_Giving, Jun_Giving, Jul_Note, Jul_P_Giving, Jul_Giving, Aug_Note,
Aug_P_Giving, Aug_Giving, Sep_Note, Sep_P_Giving, Sep_Giving, Oct_Note, Oct_P_Giving, Oct_Giving, 
Nov_Note, Nov_P_Giving, Nov_Giving, Dec_Note, Dec_P_Giving, Dec_Giving, Group_ID, Analysis_Fl)

Select A.Group_Code, A.Group_Formal_Name, A.Group_Informal_Name, 0, ' ', E.LUV_Desc, A.Mkt_Activity_ID, 
max(Y.Group_Formal_Name)  as Solicit_Nm,
(Select max(Group_Formal_Name) from Per_Group_Loc 
	where Per_Group_Loc.Group_ID = A.Mkt_Activity_ID) 
	as Mkt_Activity,
I.Org_Address, I.Org_Address2, I.Org_City, I.State_Code, I.Org_Postal_Code, 0 as Yr1_Gifts, 0 as Prev_Yr_Giving, 
0 as Prev_Yr_CC, 0 as Curr_Yr_CC, 0 as Prev_Yr_DF, 0 as Curr_Yr_DF,
0 as Yr2_Gifts, 0 as Curr_Yr_Giving,
(Select top 1 cast(substring(Notes,1,7) as integer) from Events 
	where (Org_ID = A.Group_ID or Per_ID = A.Group_ID)
	and Event_LUV_ID in (1270006,1270007)),
(Select top 1 LUV_Desc from LookUpValues, Events
	where (Org_ID = A.Group_ID or Per_ID = A.Group_ID)
	and Event_LUV_Parent_ID in (150700,258000)
	and LUV_ID = Event_LUV_ID),
(Select top 1 LUV_Desc from LookUpValues, Events
	where (Org_ID = A.Group_ID or Per_ID = A.Group_ID)
	and Event_LUV_Parent_ID in (110800,217000)
	and LUV_ID = Event_LUV_ID),
(Select top 1 LUV_Desc from LookUpValues, Events
	where (Org_ID = A.Group_ID or Per_ID = A.Group_ID)
	and Event_LUV_Parent_ID in (110900,218000)
	and LUV_ID = Event_LUV_ID),
(Select top 1 LUV_Desc  from LookUpValues, Events
	where (Org_ID = A.Group_ID or Per_ID = A.Group_ID)
	and Event_LUV_Parent_ID in (110925,218100)
	and LUV_ID = Event_LUV_ID),
(Select top 1 LUV_Desc from LookUpValues, Events
	where (Org_ID = A.Group_ID or Per_ID = A.Group_ID)
	and Event_LUV_Parent_ID in (110950,218200)
	and LUV_ID = Event_LUV_ID),
(Select top 1 Par.LUV_Desc + ':' + Chld.LUV_Desc + '-' + Msg.Rel_Desc
	from Related_Groups Msg, LookUpValues Chld, LookUpValues Par
	where Msg.Child_Group_ID = A.Group_ID
	and Msg.Rel_Class_Code in (1193000)
	and Chld.LUV_ID = Msg.Rel_Type_Code
	and Par.LUV_ID = Chld.LUV_Parent_ID
	and (Msg.Begin_Date >= @Yr3_Start_Date 
		and Msg.Begin_Date < Substring(@Yr3_Start_Date,1,5)+'02'+Substring(@Yr3_Start_Date,8,16))
	order by Msg.Begin_Date Desc), 0, 0,
(Select top 1 Par.LUV_Desc + ':' + Chld.LUV_Desc + '-' + Msg.Rel_Desc
	from Related_Groups Msg, LookUpValues Chld, LookUpValues Par
	where Msg.Child_Group_ID = A.Group_ID
	and Msg.Rel_Class_Code in (1193000)
	and Chld.LUV_ID = Msg.Rel_Type_Code
	and Par.LUV_ID = Chld.LUV_Parent_ID
	and (Msg.Begin_Date >= Substring(@Yr3_Start_Date,1,5)+'02'+Substring(@Yr3_Start_Date,8,16) 
		and Msg.Begin_Date < Substring(@Yr3_Start_Date,1,5)+'03'+Substring(@Yr3_Start_Date,8,16))
	order by Msg.Begin_Date Desc), 0, 0,
(Select top 1 Par.LUV_Desc + ':' + Chld.LUV_Desc + '-' + Msg.Rel_Desc
	from Related_Groups Msg, LookUpValues Chld, LookUpValues Par
	where Msg.Child_Group_ID = A.Group_ID
	and Msg.Rel_Class_Code in (1193000)
	and Chld.LUV_ID = Msg.Rel_Type_Code
	and Par.LUV_ID = Chld.LUV_Parent_ID
	and (Msg.Begin_Date >= Substring(@Yr3_Start_Date,1,5)+'03'+Substring(@Yr3_Start_Date,8,16) 
		and Msg.Begin_Date < Substring(@Yr3_Start_Date,1,5)+'04'+Substring(@Yr3_Start_Date,8,16))
	order by Msg.Begin_Date Desc), 0, 0,
(Select top 1 Par.LUV_Desc + ':' + Chld.LUV_Desc + '-' + Msg.Rel_Desc
	from Related_Groups Msg, LookUpValues Chld, LookUpValues Par
	where Msg.Child_Group_ID = A.Group_ID
	and Msg.Rel_Class_Code in (1193000)
	and Chld.LUV_ID = Msg.Rel_Type_Code
	and Par.LUV_ID = Chld.LUV_Parent_ID
	and (Msg.Begin_Date >= Substring(@Yr3_Start_Date,1,5)+'04'+Substring(@Yr3_Start_Date,8,16) 
		and Msg.Begin_Date < Substring(@Yr3_Start_Date,1,5)+'05'+Substring(@Yr3_Start_Date,8,16))
	order by Msg.Begin_Date Desc), 0, 0,
(Select top 1 Par.LUV_Desc + ':' + Chld.LUV_Desc + '-' + Msg.Rel_Desc
	from Related_Groups Msg, LookUpValues Chld, LookUpValues Par
	where Msg.Child_Group_ID = A.Group_ID
	and Msg.Rel_Class_Code in (1193000)
	and Chld.LUV_ID = Msg.Rel_Type_Code
	and Par.LUV_ID = Chld.LUV_Parent_ID
	and (Msg.Begin_Date >= Substring(@Yr3_Start_Date,1,5)+'05'+Substring(@Yr3_Start_Date,8,16) 
		and Msg.Begin_Date < Substring(@Yr3_Start_Date,1,5)+'06'+Substring(@Yr3_Start_Date,8,16))
	order by Msg.Begin_Date Desc), 0, 0,
(Select top 1 Par.LUV_Desc + ':' + Chld.LUV_Desc + '-' + Msg.Rel_Desc
	from Related_Groups Msg, LookUpValues Chld, LookUpValues Par
	where Msg.Child_Group_ID = A.Group_ID
	and Msg.Rel_Class_Code in (1193000)
	and Chld.LUV_ID = Msg.Rel_Type_Code
	and Par.LUV_ID = Chld.LUV_Parent_ID
	and (Msg.Begin_Date >= Substring(@Yr3_Start_Date,1,5)+'06'+Substring(@Yr3_Start_Date,8,16) 
		and Msg.Begin_Date < Substring(@Yr3_Start_Date,1,5)+'07'+Substring(@Yr3_Start_Date,8,16))
	order by Msg.Begin_Date Desc), 0, 0,
(Select top 1 Par.LUV_Desc + ':' + Chld.LUV_Desc + '-' + Msg.Rel_Desc
	from Related_Groups Msg, LookUpValues Chld, LookUpValues Par
	where Msg.Child_Group_ID = A.Group_ID
	and Msg.Rel_Class_Code in (1193000)
	and Chld.LUV_ID = Msg.Rel_Type_Code
	and Par.LUV_ID = Chld.LUV_Parent_ID
	and (Msg.Begin_Date >= Substring(@Yr3_Start_Date,1,5)+'07'+Substring(@Yr3_Start_Date,8,16) 
		and Msg.Begin_Date < Substring(@Yr3_Start_Date,1,5)+'08'+Substring(@Yr3_Start_Date,8,16))
	order by Msg.Begin_Date Desc), 0, 0,
(Select top 1 Par.LUV_Desc + ':' + Chld.LUV_Desc + '-' + Msg.Rel_Desc
	from Related_Groups Msg, LookUpValues Chld, LookUpValues Par
	where Msg.Child_Group_ID = A.Group_ID
	and Msg.Rel_Class_Code in (1193000)
	and Chld.LUV_ID = Msg.Rel_Type_Code
	and Par.LUV_ID = Chld.LUV_Parent_ID
	and (Msg.Begin_Date >= Substring(@Yr3_Start_Date,1,5)+'08'+Substring(@Yr3_Start_Date,8,16) 
		and Msg.Begin_Date < Substring(@Yr3_Start_Date,1,5)+'09'+Substring(@Yr3_Start_Date,8,16))
	order by Msg.Begin_Date Desc), 0, 0,
(Select top 1 Par.LUV_Desc + ':' + Chld.LUV_Desc + '-' + Msg.Rel_Desc
	from Related_Groups Msg, LookUpValues Chld, LookUpValues Par
	where Msg.Child_Group_ID = A.Group_ID
	and Msg.Rel_Class_Code in (1193000)
	and Chld.LUV_ID = Msg.Rel_Type_Code
	and Par.LUV_ID = Chld.LUV_Parent_ID
	and (Msg.Begin_Date >= Substring(@Yr3_Start_Date,1,5)+'09'+Substring(@Yr3_Start_Date,8,16) 
		and Msg.Begin_Date < Substring(@Yr3_Start_Date,1,5)+'10'+Substring(@Yr3_Start_Date,8,16))
	order by Msg.Begin_Date Desc), 0, 0,
(Select top 1 Par.LUV_Desc + ':' + Chld.LUV_Desc + '-' + Msg.Rel_Desc
	from Related_Groups Msg, LookUpValues Chld, LookUpValues Par
	where Msg.Child_Group_ID = A.Group_ID
	and Msg.Rel_Class_Code in (1193000)
	and Chld.LUV_ID = Msg.Rel_Type_Code
	and Par.LUV_ID = Chld.LUV_Parent_ID
	and (Msg.Begin_Date >= Substring(@Yr3_Start_Date,1,5)+'10'+Substring(@Yr3_Start_Date,8,16) 
		and Msg.Begin_Date < Substring(@Yr3_Start_Date,1,5)+'11'+Substring(@Yr3_Start_Date,8,16))
	order by Msg.Begin_Date Desc), 0, 0,
(Select top 1 Par.LUV_Desc + ':' + Chld.LUV_Desc + '-' + Msg.Rel_Desc
	from Related_Groups Msg, LookUpValues Chld, LookUpValues Par
	where Msg.Child_Group_ID = A.Group_ID
	and Msg.Rel_Class_Code in (1193000)
	and Chld.LUV_ID = Msg.Rel_Type_Code
	and Par.LUV_ID = Chld.LUV_Parent_ID
	and (Msg.Begin_Date >= Substring(@Yr3_Start_Date,1,5)+'11'+Substring(@Yr3_Start_Date,8,16) 
		and Msg.Begin_Date < Substring(@Yr3_Start_Date,1,5)+'12'+Substring(@Yr3_Start_Date,8,16))
	order by Msg.Begin_Date Desc), 0, 0,
(Select top 1 Par.LUV_Desc + ':' + Chld.LUV_Desc + '-' + Msg.Rel_Desc
	from Related_Groups Msg, LookUpValues Chld, LookUpValues Par
	where Msg.Child_Group_ID = A.Group_ID
	and Msg.Rel_Class_Code in (1193000)
	and Chld.LUV_ID = Msg.Rel_Type_Code
	and Par.LUV_ID = Chld.LUV_Parent_ID
	and (Msg.Begin_Date >= Substring(@Yr3_Start_Date,1,5)+'12'+Substring(@Yr3_Start_Date,8,16) 
		and Msg.Begin_Date < @Yr4_Start_Date)
	order by Msg.Begin_Date Desc), 0, 0, A.Group_ID, 'Y'

FROM Per_Group_Loc A 
LEFT OUTER JOIN Payment_Device B ON B.Payor_Code = A.Group_Code 
LEFT OUTER JOIN Payment_Device_History C ON C.Pay_Device_ID = B.Pay_Device_ID 
LEFT OUTER JOIN Payment D ON D.Payment_ID = C.Payment_ID 
LEFT OUTER JOIN Payment_Allocation F ON F.Payment_ID = D.Payment_ID 
LEFT OUTER JOIN Payment_Batch H ON H.Batch_ID = D.Batch_ID
						and H.Process_Date between @Yr1_Start_Date and @Yr4_Start_Date
INNER JOIN LookUpValues E ON E.LUV_ID = A.Group_Type_Code
LEFT OUTER JOIN Org_Addresses I ON I.Org_Addr_ID = A.Primary_Mail_Addr_ID
INNER JOIN Related_Groups X ON X.Child_Group_ID = A.Group_ID
INNER JOIN Per_Group_Loc Y ON Y.Group_ID = X.Parent_Group_ID

WHERE ((A.Group_Type_Code not in (411050, 1143105) and @Ind_Flag = 'Y')
	or (A.Group_Type_Code in (411050) and @Org_Flag = 'Y')
	or (A.Group_Type_Code in (1143105) and @Found_Flag = 'Y'))
and A.Active = 1
and A.Group_ID not in (select Org_ID from Events where Org_ID = A.Group_ID and Event_LUV_ID in (218050,110905)
					union select Per_ID from Events where Per_ID = A.Group_ID and Event_LUV_ID in (218050,110905))
and X.Rel_Type_Code in (1120898)
and X.Valid_Flag = 'V'

Group by A.Group_ID, A.Group_Code, A.Group_Type_Code, A.Group_Formal_Name, A.Group_Informal_Name, E.LUV_Desc,
A.Mkt_Activity_ID, I.Org_Address, I.Org_Address2, I.Org_City, I.State_Code, I.Org_Postal_Code

ORDER BY A.Group_Formal_Name

==> Insert into #Mkt_Activity_Summary (Group_Cd, Org_Name, Inf_Name, Soft_Group_Cd, Soft_Org_Name, Group_Type, Mkt_Activity_ID,
Solicitor_Nm, Mkt_Activity, Org_Addr_1, Org_Addr_2, Org_City, Org_State, Org_Zip, Group_ID, Analysis_Fl)
 
==> Select A.Group_Cd, A.Org_Name, A.Inf_Name, C.Group_Code as Sft_Grp_Cd, C.Group_Formal_Name as Sft_Name, D.LUV_Desc,
C.Mkt_Activity_ID, A.Solicitor_Nm, E.Group_Formal_Name as Mkt_Activity, F.Org_Address, F.Org_Address2, F.Org_City, 
F.State_Code, F.Org_Postal_Code, C.Group_ID, 'N'
from #Mkt_Activity_Summary A
inner join Related_Groups B on B.Parent_Group_ID =  A.Group_ID
inner join Per_Group_Loc C on C.Group_ID = B.Child_Group_ID
inner join LookUpValues D on D.LUV_ID = C.Group_Type_Code 
inner join Per_Group_Loc E on E.Group_ID = C.Mkt_Activity_ID
left outer join Org_Addresses F on F.Org_Addr_ID = C.Primary_Mail_Addr_ID
where B.Rel_Type_Code in (1270005)
and B.Valid_Flag = 'V'

==> Select Group_Cd, Org_Name, Inf_Name, Soft_Group_Cd, Soft_Org_Name, Group_Type, Solicitor_Nm, Mkt_Activity, 
Org_Addr_1, Org_Addr_2, Org_City, Org_State, Org_Zip, Prev_Yr_DF, Curr_Yr_DF, Prev_Yr_CC, 
Curr_Yr_CC, Prev_Yr_Gifts, Prev_Yr_Giving, Curr_Yr_Gifts, Curr_Yr_Giving, Giving_Goal, Elim_Connection, 
Cap_Range, Lvl_Ngage, Disab_Aware, Giving_Status, Jan_Note, Jan_P_Giving, Jan_Giving, Feb_Note, Feb_P_Giving, 
Feb_Giving, Mar_Note, Mar_P_Giving, Mar_Giving, Apr_Note, Apr_P_Giving, Apr_Giving, May_Note, May_P_Giving, 
May_Giving, Jun_Note, Jun_P_Giving, Jun_Giving, Jul_Note, Jul_P_Giving, Jul_Giving, Aug_Note,
Aug_P_Giving, Aug_Giving, Sep_Note, Sep_P_Giving, Sep_Giving, Oct_Note, Oct_P_Giving, Oct_Giving, 
Nov_Note, Nov_P_Giving, Nov_Giving, Dec_Note, Dec_P_Giving, Dec_Giving

from #Mkt_Activity_Summary

Order by Solicitor_Nm, Group_Type, Org_Name, Soft_Org_Name

drop Table #Mkt_Activity_Summary

Any assistance is greatly appreciated!!!

Phil

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-04-26 : 13:26:31

Sorry, I can parse your code without errors after deleting the "==>".

No, you're never too old to Yak'n'Roll if you're too young to die.

lueylump
Starting Member

25 Posts

Posted - 2011-04-26 : 14:00:08

Interesting! But thanks for trying!!!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-04-26 : 14:34:14

Parsing won't show invalid column errors, so you're going to have to give us all the definitions of all the permanent tables so that we can run the code.

--
Gail Shaw
SQL Server MVP

lueylump
Starting Member

25 Posts

Posted - 2011-04-27 : 11:56:15

I copied and pasted my code into another window and it worked. This was definitely a wierd problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources