CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Jack
Jack

Reputation: 7547

Ambiguous column name error in order by clause

Why doesn't this query work?

SELECT ROW_NUMBER() OVER (
        ORDER BY DocumentID
        ) peta_rn
    , d.DocumentID
    , d.IsReEfiled
    , d.IGroupID
    , d.ITypeID
    , d.RecordingDateTime
    , dbo.GetLatestStatusDateTime(d.DocumentID) AS LatestStatusDatetime
    , dbo.FnCanChangeDocumentStatus(d.DocumentStatusID, d.DocumentID) AS CanChangeStatus
    , d.IDate
    , d.InstrumentID
    , d.DocumentStatusID
    , ig.Abbreviation AS IGroupAbbreviation
    , u.Username
    , j.JDAbbreviation
    , inf.DocumentName
    , it.Abbreviation AS ITypeAbbreviation
    , d.DocumentDate
    , ds.Abbreviation AS DocumentStatusAbbreviation
    , dbo.GetFlatDocumentName(d.DocumentID) AS FlatDocumentName
FROM Documents d
INNER JOIN IGroupes ig
    ON d.IGroupID = ig.IGroupID
LEFT JOIN ITypes it
    ON d.ITypeID = it.ITypeID
LEFT JOIN Users u
    ON u.UserID = d.UserID
LEFT JOIN DocumentStatuses ds
    ON d.DocumentStatusID = ds.DocumentStatusID
LEFT JOIN InstrumentFiles inf
    ON d.DocumentID = inf.DocumentID
INNER JOIN Jurisdictions j
    ON j.JurisdictionID = d.JurisdictionID
WHERE 1 = 1

whereas this one works:

SELECT ROW_NUMBER() OVER (
        ORDER BY d.DocumentID
        ) peta_rn
    , d.DocumentID
    , d.IsReEfiled
    , d.IGroupID
    , d.ITypeID
    , d.RecordingDateTime
    , dbo.GetLatestStatusDateTime(d.DocumentID) AS LatestStatusDatetime
    , dbo.FnCanChangeDocumentStatus(d.DocumentStatusID, d.DocumentID) AS CanChangeStatus
    , d.IDate
    , d.InstrumentID
    , d.DocumentStatusID
    , ig.Abbreviation AS IGroupAbbreviation
    , u.Username
    , j.JDAbbreviation
    , inf.DocumentName
    , it.Abbreviation AS ITypeAbbreviation
    , d.DocumentDate
    , ds.Abbreviation AS DocumentStatusAbbreviation
    , dbo.GetFlatDocumentName(d.DocumentID) AS FlatDocumentName
FROM Documents d
INNER JOIN IGroupes ig
    ON d.IGroupID = ig.IGroupID
LEFT JOIN ITypes it
    ON d.ITypeID = it.ITypeID
LEFT JOIN Users u
    ON u.UserID = d.UserID
LEFT JOIN DocumentStatuses ds
    ON d.DocumentStatusID = ds.DocumentStatusID
LEFT JOIN InstrumentFiles inf
    ON d.DocumentID = inf.DocumentID
INNER JOIN Jurisdictions j
    ON j.JurisdictionID = d.JurisdictionID
WHERE 1 = 1

As you can see the only difference is I specify d.DocumentID. Is there anyway I can avoid having to specify d.DocumentID and instead specify only DocumentID without using an extra Select? because after all, all documentID refer to the same value.

Upvotes: 0

Views: 2546

Answers (2)

Adriaan Stander
Adriaan Stander

Reputation: 166336

The problem is that DocumentID is in both tables InstrumentFiles and Documents, so how would the query engine know which one you are referring to?

So in short, No, you have to specify d.DocumentID in this instance.

Upvotes: 4

John Woo
John Woo

Reputation: 263693

No.

you should have provided the alias d since multiple tables contains DocumentID. The engine gets confused on where (or which table) the order should be taken.

Upvotes: 2

Related Questions