Generates Msg 107, Level 16, State 2, Line 2 - column prefix does not exist (Oh yes it does.. anyone got a solution ?)

Transact-SQL

DB007


hi All,

Following code generates error 107 (SQL Server 2000, SP3a):

SELECT

*

FROM

dbo.AssessmentItemHierarchy Parent

INNER JOIN dbo.RaterCategory

INNER JOIN dbo.RaterType

ON RaterCategory.ID = RaterType.RaterCategoryID

INNER JOIN dbo.AssessmentRater

ON AssessmentRater.RaterTypeID = RaterType.ID

INNER JOIN dbo.AssessmentItem

ON AssessmentItem.ID = Parent.ChildItemID

INNER JOIN dbo.Assessment

ON AssessmentItem.AssessmentID = Assessment.ID

INNER JOIN dbo.AssessmentResponse

ON AssessmentResponse.AssessmentItemID = AssessmentItem.ID

ON AssessmentResponse.AssessmentRaterID = AssessmentRater.ID

INNER JOIN dbo.ImageInstance

ON ImageInstance.ID = RaterType.ImageInstanceID

INNER JOIN dbo.AssessmentItem ParentGUID

ON Parent.ParentItemID = ParentGUID.ID

Error on Run:

Msg 107, Level 16, State 2, Line 2

The column prefix 'Parent' does not match with a table name or alias name used in the query.

The only work-around I have found is a rewrite of order:

SELECT

*

FROM

AssessmentItemHierarchy Parent

INNER JOIN RaterCategory

INNER JOIN RaterType

INNER JOIN AssessmentRater

INNER JOIN AssessmentItem

INNER JOIN Assessment ON AssessmentItem.AssessmentID = Assessment.ID

INNER JOIN AssessmentResponse ON AssessmentItem.ID = AssessmentResponse.AssessmentItemID

ON AssessmentRater.ID = AssessmentResponse.AssessmentRaterID

ON RaterType.ID = AssessmentRater.RaterTypeID

ON RaterCategory.ID = RaterType.RaterCategoryID

INNER JOIN ImageInstance

ON RaterType.ImageInstanceID = ImageInstance.ID

ON Parent.ChildItemID = AssessmentItem.ID

INNER JOIN AssessmentItem ParentGUID ON Parent.ParentItemID = ParentGUID.ID

Not very pretty... anyone know why I'm getting this error message with the first version of my query



Top

Re: Generates Msg 107, Level 16, State 2, Line 2 - column prefix does not exist (Oh yes it does.. anyone got a solution ?)

Arnie Rowland


In the first query, the way you have the JOINs nested, the AssessmentItemHierachy (Parent) has not yet been joined, and is not available when you attempt the JOIN with AssessmentItem.

Examine the execution plan to better understand the order of precedence you have established the way the JOINs are ordered.

I suggest, that since you have to ask this question, it would be better to keep each JOIN immediately followed by the JOIN conditions for those two tables, and NOT write them as you are doing. IT is much more difficult to understand and 'control' the results when you start nesting JOINs. Alternatively, if you are deliberately nesting the JOINs in order to better control the results, you may want to use parantheses to better pre-determine the order of precedence.






Top