I am modifying an existing stored procedure in SQL server 2005. I have added a new field to the sp and am now receiving an ambiguous column name message. The column being referred to was in the sp before I modified. The column is on the line above where I added my new field ( EMPLOYEE NUMBER) to the sp. I am at a loss to why I'm getting this error message when executing the sp because this column existed before I modified. Can anyone help me understand why I'm getting this message all of a sudden and/ or where to look for help? Thanks in advance for any light you can help shed on this matter. Code snippet is below:
[BILL DUE DATE], [PAYMENT DATE AND TIME],
[EMPLOYEE NUMBER] )
Msg 209, Level 16, State 1, Procedure spBuildNoReasonLetter, Line 34
Ambiguous column name 'PAYMENT DATE AND TIME'.
There is no way we can 'guess' what is happening without seeing the code.
Please post the entire stored procedure code.
|||
Sorry, here is the sp:
setANSI_NULLSON
setQUOTED_IDENTIFIERON
go
ALTERPROCEDURE [dbo].[spBuildNoReasonLetter]
AS
setnocounton
TRUNCATETABLE [NO REASON LETTER];
INSERTINTO [NO REASON LETTER] ( [EMPLOYER NUMBER], [EMPLOYER NAME],
[CONTACT PERSON], [ADDRESS LINE 1],
[ADDRESS LINE 2], [ADDRESS LINE 3],
[EMPLOYEE SSN], [SERVICE CODE],
[EMPLOYEE NAME], [BILLING PERIOD],
[BILL DUE DATE], [PAYMENT DATE AND TIME],
[EMPLOYEE NUMBER] )
SELECTDISTINCT [NIGHT BATCH TABLE].[EMPLOYER NUMBER],
[COMPANY ADDRESS].[FULL NAME] AS [EMPLOYER NAME],
[COMPANY ADDRESS].[CONTACT PERSON],
[COMPANY ADDRESS].[ADDRESS LINE 1],
[COMPANY ADDRESS].[ADDRESS LINE 2],
[COMPANY ADDRESS].[CITY]+', '+[COMPANY ADDRESS].[STATE]+' '+[COMPANY ADDRESS].[ZIP CODE] AS [ADDRESS LINE 3],
[NIGHT BATCH TABLE].[EMPLOYEE SSN],
[NIGHT BATCH TABLE].[SERVICE CODE],
[MAIN EMPLOYEE].[FULL NAME] AS [EMPLOYEE NAME],Convert(varchar(10),
[COMPANY PAY TABLE].[BILLING PERIOD START],101)+' Thru '+Convert(varchar(10),[COMPANY PAY TABLE].[BILLING PERIOD END],101)AS [BILLING PERIOD],
[NIGHT BATCH TABLE].[BILL DUE DATE], [PAYMENT DATE AND TIME],
[MAIN EMPLOYEE].[EMPLOYEE NUMBER]
FROM(([NIGHT BATCH TABLE] INNERJOIN [COMPANY ADDRESS] ON([NIGHT BATCH TABLE].[EMPLOYER NUMBER] = [COMPANY ADDRESS].[ADDRESS KEY])AND
([NIGHT BATCH TABLE].[ADDRESS TYPE] = [COMPANY ADDRESS].[ADDRESS TYPE]))
INNERJOIN [MAIN EMPLOYEE] ON [NIGHT BATCH TABLE].[EMPLOYEE SSN] = [MAIN EMPLOYEE].[EMPLOYEE SSN])
INNERJOIN [NO REASON LETTER] ON [MAIN EMPLOYEE].[EMPLOYEE NUMBER] = [NO REASON LETTER].[EMPLOYEE NUMBER]
INNERJOIN [COMPANY PAY TABLE] ON([NIGHT BATCH TABLE].[BILL DUE DATE] = [COMPANY PAY TABLE].[BILL DUE DATE])AND
([NIGHT BATCH TABLE].[EMPLOYER NUMBER] = [COMPANY PAY TABLE].[EMPLOYER NUMBER])
WHERE([NIGHT BATCH TABLE].[REASON CODE]='X'AND
[MAIN EMPLOYEE].[STATUS CODE] In('00','01','09'));
UPDATE NRL
SET NRL.[CARRIER NAME] = CA.[FULL NAME],
NRL.[LOGO PATH] = CA.[CONTACT PERSON],
NRL.[TOLL FREE SERVICE NO] = CA.[TOLL FREE SERVICE NO]
FROM [NO REASON LETTER] AS NRL INNERJOIN [COMPANY ADDRESS] AS CA ON(CA.[ADDRESS KEY] = NRL.[EMPLOYER NUMBER] AND
CA.[ADDRESS TYPE] ='R')
UPDATE NRL
SET NRL.[SORT FIELD1] = dbo.fnReturnNRLSortField(CO.[SORT BILL BY],
ME.[FULL NAME],
ME.[EMPLOYEE SSN],
ME.[EMPLOYEE NUMBER],
ME.[DEPARTMENT CODE],
ME.[LOCATION CODE], 1),
NRL.[SORT FIELD2] = dbo.fnReturnNRLSortField(CO.[SORT BILL BY],
ME.[FULL NAME],
ME.[EMPLOYEE SSN],
ME.[EMPLOYEE NUMBER],
ME.[DEPARTMENT CODE],
ME.[LOCATION CODE], 2),
NRL.[EMPLOYEE SSN] = dbo.fnReturnFieldOrBlank(ME.[EMPLOYEE SSN], CO.[DO NOT DISPLAY SSN])
FROM [NO REASON LETTER] AS NRL INNERJOIN [COMPANY] AS CO ON(CO.[EMPLOYER NUMBER] = NRL.[EMPLOYER NUMBER])
INNERJOIN [MAIN EMPLOYEE] AS ME ON(ME.[EMPLOYEE SSN] = NRL.[EMPLOYEE SSN])
INNERJOIN [MAIN EMPLOYEE] AS ME ON(ME.[EMPLOYEE NUMBER] = NRL.[EMPLOYEE NUMBER])
|||I have changed my code slightly and am now getting a differenet message (new code is below error message:
Msg 4104, Level 16, State 1, Procedure spBuildNoReasonLetter, Line 17
The multi-part identifier "NO REASON LETTER.EMPLOYEE NUMBER" could not be bound.
setANSI_NULLSON
setQUOTED_IDENTIFIERON
go
ALTERPROCEDURE [dbo].[spBuildNoReasonLetter]
AS
setnocounton
TRUNCATETABLE [NO REASON LETTER];
INSERTINTO [NO REASON LETTER] ( [EMPLOYER NUMBER], [EMPLOYER NAME],
[CONTACT PERSON], [ADDRESS LINE 1],
[ADDRESS LINE 2], [ADDRESS LINE 3],
[EMPLOYEE SSN], [SERVICE CODE],
[EMPLOYEE NAME], [BILLING PERIOD],
[BILL DUE DATE], [PAYMENT DATE AND TIME],
[EMPLOYEE NUMBER])
SELECTDISTINCT [NIGHT BATCH TABLE].[EMPLOYER NUMBER],
[COMPANY ADDRESS].[FULL NAME] AS [EMPLOYER NAME],
[COMPANY ADDRESS].[CONTACT PERSON],
[COMPANY ADDRESS].[ADDRESS LINE 1],
[COMPANY ADDRESS].[ADDRESS LINE 2],
[COMPANY ADDRESS].[CITY]+', '+[COMPANY ADDRESS].[STATE]+' '+[COMPANY ADDRESS].[ZIP CODE] AS [ADDRESS LINE 3],
[NIGHT BATCH TABLE].[EMPLOYEE SSN],
[NIGHT BATCH TABLE].[SERVICE CODE],
[MAIN EMPLOYEE].[FULL NAME] AS [EMPLOYEE NAME],Convert(varchar(10),
[COMPANY PAY TABLE].[BILLING PERIOD START],101)+' Thru '+Convert(varchar(10),[COMPANY PAY TABLE].[BILLING PERIOD END],101)AS [BILLING PERIOD],
[NIGHT BATCH TABLE].[BILL DUE DATE], [PAYMENT DATE AND TIME],
[MAIN EMPLOYEE].[EMPLOYEE NUMBER]
FROM(([NIGHT BATCH TABLE] INNERJOIN [COMPANY ADDRESS] ON([NIGHT BATCH TABLE].[EMPLOYER NUMBER] = [COMPANY ADDRESS].[ADDRESS KEY])AND
([NIGHT BATCH TABLE].[ADDRESS TYPE] = [COMPANY ADDRESS].[ADDRESS TYPE]))
INNERJOIN [MAIN EMPLOYEE] ON [NIGHT BATCH TABLE].[EMPLOYEE SSN] = [MAIN EMPLOYEE].[EMPLOYEE SSN])
INNERJOIN [MAIN EMPLOYEE] AS MET ON [NO REASON LETTER].[EMPLOYEE NUMBER] = [MAIN EMPLOYEE].[EMPLOYEE NUMBER]
INNERJOIN [COMPANY PAY TABLE] ON([NIGHT BATCH TABLE].[BILL DUE DATE] = [COMPANY PAY TABLE].[BILL DUE DATE])AND
([NIGHT BATCH TABLE].[EMPLOYER NUMBER] = [COMPANY PAY TABLE].[EMPLOYER NUMBER])
WHERE([NIGHT BATCH TABLE].[REASON CODE]='X'AND
[MAIN EMPLOYEE].[STATUS CODE] In('00','01','09'));
UPDATE NRL
SET NRL.[CARRIER NAME] = CA.[FULL NAME],
NRL.[LOGO PATH] = CA.[CONTACT PERSON],
NRL.[TOLL FREE SERVICE NO] = CA.[TOLL FREE SERVICE NO]
FROM [NO REASON LETTER] AS NRL INNERJOIN [COMPANY ADDRESS] AS CA ON(CA.[ADDRESS KEY] = NRL.[EMPLOYER NUMBER] AND
CA.[ADDRESS TYPE] ='R')
UPDATE NRL
SET NRL.[SORT FIELD1] = dbo.fnReturnNRLSortField(CO.[SORT BILL BY],
ME.[FULL NAME],
ME.[EMPLOYEE SSN],
MET.[EMPLOYEE NUMBER],
ME.[DEPARTMENT CODE],
ME.[LOCATION CODE], 1),
NRL.[SORT FIELD2] = dbo.fnReturnNRLSortField(CO.[SORT BILL BY],
ME.[FULL NAME],
ME.[EMPLOYEE SSN],
MET.[EMPLOYEE NUMBER],
ME.[DEPARTMENT CODE],
ME.[LOCATION CODE], 2),
NRL.[EMPLOYEE SSN] = dbo.fnReturnFieldOrBlank(ME.[EMPLOYEE SSN], CO.[DO NOT DISPLAY SSN])
FROM [NO REASON LETTER] AS NRL INNERJOIN [COMPANY] AS CO ON(CO.[EMPLOYER NUMBER] = NRL.[EMPLOYER NUMBER])
INNERJOIN [MAIN EMPLOYEE] AS ME ON(ME.[EMPLOYEE SSN] = NRL.[EMPLOYEE SSN])
INNERJOIN [MAIN EMPLOYEE] AS MET ON(ME.[EMPLOYEE NUMBER] = NRL.[EMPLOYEE NUMBER])
|||
Hi,
you are not referencing the Table in the Select clause, therefore you cannot use it in the join part.
BTW. Did I mention that it is horrorible to use space and special characters in defintions ?
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||
Now I'm confused.
First you TRUNCATE the [NO REASON LETTER] table.
Then you attempt to use JOIN conditions to that EMPTY table (but there is no previous mention of a JOIN to that table.. What are you trying to accomplish?
| |
| INNERJOIN [MAIN EMPLOYEE] AS MET ON [NO REASON LETTER].[EMPLOYEE NUMBER] = [MAIN EMPLOYEE].[EMPLOYEE NUMBER] |
Even if it 'could' happen (and it just can't), since the table is empty, this would serve to filter out ALL possible rows and nothing would be inserted.
So what's the point?
|||
One other thing to keep in mind is that if you are going to Alias a table in a join clause, you probably should use it in the join.
INNERJOIN [MAIN EMPLOYEE] AS MET ON(ME.[EMPLOYEE NUMBER] = NRL.[EMPLOYEE NUMBER])
I would think that you would want to use MET.[EMPLOYEE NUMBER] instead of ME.
Ben Miller
ivanchain wrote: