T-SQL statement with SubSelect and Join - Ambiguous column name error

Question

I've spent hours researching answers to what should be a very simple T-SQL statement.
Here is the situation. I'm tyring to report over a help desk data base. I have one table (calllog) that contains basic non-repeating information about a trouble ticket and another transactional table (Asgnmnt) which contains various assignments to different people to fix the problem. I'm simply trying to determine the last assigment in the Asgnmnt table, link it with the call log file and use that data in the outer select to do more data reporting. The Asgnmnt table contains a field call HEATSeq. The hightest HEATSeq for a given CallID will be the last assignment. Here is my T-SQL statement and error.

SELECT
Callid,
Priority,
RecvdDate,
RecvdTime,
ClosedDate,
ClosedTime,
CustID,
CallType,
CallDesc,
HeatSEQ#
FROM
      (Select 
      a.Callid, 
      a.Priority,
      a.RecvdDate,
      a.RecvdTime,
      a.ClosedDate,
      a.ClosedTime,
      a.CustID,
      a.CallType,
      a.CallDesc,
      (select TOP 1 HEATSeq from Asgnmnt f
      WHERE  a.CallID = f.CallID
      ORDER BY HEATSeq desc) as HeatSeq#
      FROM dbo.CallLog a 
      where a.CallID = '00520308') as z
LEFT OUTER JOIN dbo.Asgnmnt c on z.Callid=c.CallID and z.HeatSeq# = c.HEATSeq

Here is the [ERROR]

Msg 209, Level 16, State 1, Line 2
Ambiguous column name 'Callid'.
Msg 209, Level 16, State 1, Line 9
Ambiguous column name 'CallType'

Tim Lehner · Answer 1 · 2013-04-04T18:11:39.750

7

You probably have Callid and CallType (at least) in both z and c. Use the table aliases for at least those columns in your outer query:

SELECT
z.Callid, -- No longer ambiguous
z.Priority,
z.RecvdDate,
z.RecvdTime,
z.ClosedDate,
z.ClosedTime,
z.CustID,
z.CallType,
z.CallDesc,
z.HeatSEQ#
FROM
      (Select 
      a.Callid, 
      a.Priority,
      a.RecvdDate,
      a.RecvdTime,
      a.ClosedDate,
      a.ClosedTime,
      a.CustID,
      a.CallType,
      a.CallDesc,
      (select TOP 1 HEATSeq from Asgnmnt f
      WHERE  a.CallID = f.CallID
      ORDER BY HEATSeq desc) as HeatSeq#
      FROM dbo.CallLog a 
      where a.CallID = '00520308') as z
LEFT OUTER JOIN dbo.Asgnmnt c on z.Callid=c.CallID and z.HeatSeq# = c.HEATSeq

edited Apr 04 '13 at 18:11

answered Apr 04 '13 at 17:58

Tim Lehner

14,813
4
59
76

I think we are getting closer, but there is still a problem. Here is the NEW error: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ','. Msg 156, Level 15, State 1, Line 26 Incorrect syntax near the keyword 'as'. – user2174861 Apr 04 '13 at 19:36
I Sincerely appologize, Your fix work GREAT. I did not copy the SELECT portion of the statement, ALL is Working GREAT!!!!! Thanks very much for you speedy response. – user2174861 Apr 04 '13 at 19:56
@user2174861, that's great. Feel free to upvote or mark an answer as best to help future visitors. – Tim Lehner Apr 05 '13 at 16:01

score 2 · Answer 2 · answered Apr 04 '13 at 18:04

You should fully qualify all column names in your outer SELECT with appropriate table aliases from the FROM clause, e.g. z.Callid or c.Callid. Since you are left joining on Callid, there may be rows in the result set where z.Callid is not null but c.Callid is null. Same is true for CallType.

T-SQL statement with SubSelect and Join - Ambiguous column name error

2 Answers2