March 18, 2004 at 4:46 pm
Hello,
I have an INSERT SELECT statement that attempts to Insert values into a table based on the query of several other tables. Normally I don't have problems with this operation, but I'm getting 'Ambiguous Column Name' errors for this particular INSERT. I've tried using aliases for the column names I'm inserting into, and I've tried setting up the query after the SELECT statement as an Inline query, but nothing has worked.
The table I am inserting into is called 'tblMMrptStanResultsGrade'. Two of the fields from this table are not included in the insert statement. One of the fields in this table is called 'RecID' and is an INT IDENTITY field. The other field is called 'InsertDate' and is 'smallDateTime' with a default of GETDATE().
Below is one version of the syntax of the INSERT ... SELECT statement:
********************************
INSERT tblMMrptStanResultsGrade
(LocationDesc, Location2, TLastName, TFirstName,
SLastName, SFirstName, Permnum, Grade, TestDesc, TestShortName,
QID, Score, StanID, Domain, Strand, StanNum,
SGroup, SubStrand, StanDesc, gDesc)
SELECT
LC.LocationDesc,
LC.Location2,
TD.LastName AS TLastName,
TD.FirstName AS TFirstName,
SD.LastName AS SLastName,
SD.FirstName AS SFirstName,
SD.Permnum As SPermnum,
STS.Grade,
TT.TestDesc,
TT.TestShortName,
TS.QID,
TS.Score,
STD.StanID,
STS.Domain,
STS.Strand,
STS.StanNum,
STS.SGroup,
STS.SubStrand,
STS.StanDesc,
SG.gDesc
FROM tblLocation LC
Inner Join Student_Data_Main SD On LC.Location2=SD.SchoolNum
Inner Join Teacher_Data_Main TD On TD.TeacherID=SD.TeacherID
Inner Join tblMMStanTestScores TS On SD.Permnum=TS.Permnum
Inner Join tblMMStateStandardsData STD On STD.QID=TS.QID
and STD.TestShortName=TS.TestShortName
Inner Join tblTests TT On TT.TestShortName=STD.TestShortName
Inner Join tblMMStateStandards STS On STS.Grade=TS.Grade
and STS.StanID=STD.StanID
Inner Join tblMMStandardsGroups SG On SG.gGrade=STS.Grade
and SG.gGroupID=STS.SGroup
WHERE
LC.Location2 = 371 and TS.grade = 2
ORDER BY LocationDesc, Location2, TLastName, TFirstName,
SLastName, SFirstName, Permnum,
Grade, TestShortName, QID, Domain, Strand
**************************************************
When I attempt to run this query as it is, I get the following messages:
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Permnum'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'Grade'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'TestShortName'.
Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'QID'.
What simple thing am I forgetting here that would make this query work?
Thanks for your help!
CSDunn
March 19, 2004 at 12:41 am
Hello CSDunn
The error you are getting lies in the order by clause statement.
FROM tblLocation LC
Inner Join Student_Data_Main SD On LC.Location2=SD.SchoolNum
Inner Join Teacher_Data_Main TD On TD.TeacherID=SD.TeacherID
Inner Join tblMMStanTestScores TS On SD.Permnum=TS.Permnum
Inner Join tblMMStateStandardsData STD On STD.QID=TS.QID
and STD.TestShortName=TS.TestShortName
Inner Join tblTests TT On TT.TestShortName=STD.TestShortName
Inner Join tblMMStateStandards STS On STS.Grade=TS.Grade
and STS.StanID=STD.StanID
Inner Join tblMMStandardsGroups SG On SG.gGrade=STS.Grade
and SG.gGroupID=STS.SGroup
ORDER BY LocationDesc, Location2, TLastName, TFirstName,
SLastName, SFirstName, Permnum,
Grade, TestShortName, QID, Domain, Strand
Please see that the column names which you have referred in the order by clause statement is referring to
Permnum (tblMMStanTestScores, Student_Data_Main)
Grade (tblMMStateStandards, tblMMStanTestScores)
TestShortName (tblTests, tblMMStateStandardsData)
QID (tblMMStateStandardsData, tblMMStanTestScores)
So the order by clause statement columns should refer to either one table. Change the order by statement as follows:
======================================================
ORDER BY LocationDesc, Location2, TLastName, TFirstName,
SLastName, SFirstName, ts.Permnum,
ts.Grade, tt.TestShortName, ts.QID, Domain, Strand
======================================================
and your query works fine.
Lucky
March 19, 2004 at 10:55 am
Thanks for your help. It didn't occur to me that ambiguity could be a problem for fields used in the Order By clause if matching field names are used in joins. Believe it or not, I don't think I've ever had this problem before.
Normally, the ambiguity problem I run into is with matching field names from multiple tables where some/all of those matching fields appear in the field list of a select statement.
CSDunn
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply