"Ambiguous Column Name" Error on One Particular Server

Ambiguous column name error on one particular server

Wow. The problem was with the database compatibility mode. It was set to "80" (sql 2000). I've set it to 90 and the query works fine now.

More info on compatibility levels can be found here:
http://msdn.microsoft.com/en-US/library/ms178653(SQL.90).aspx

Query error with ambiguous column name in SQL

We face this error when we are selecting data from more than one tables by joining tables and at least one of the selected columns (it will also happen when use * to select all columns) exist with same name in more than one tables (our selected/joined tables). In that case we must have to specify from which table we are selecting out column.

Following is a an example solution implementation of concept explained above

I think you have ambiguity only in InvoiceID that exists both in InvoiceLineItems and Invoices Other fields seem distinct. So try This

I just replace InvoiceID with Invoices.InvoiceID

   SELECT 
        VendorName, Invoices.InvoiceID, InvoiceSequence, InvoiceLineItemAmount
    FROM Vendors 
    JOIN Invoices ON (Vendors.VendorID = Invoices.VendorID)
    JOIN InvoiceLineItems ON (Invoices.InvoiceID = InvoiceLineItems.InvoiceID)
    WHERE  
        Invoices.InvoiceID IN
            (SELECT InvoiceSequence 
             FROM InvoiceLineItems
             WHERE InvoiceSequence > 1)
    ORDER BY 
        VendorName, Invoices.InvoiceID, InvoiceSequence, InvoiceLineItemAmount

You can use tablename.columnnae for all columns (in selection,where,group by and order by) without using any alias. However you can use an alias as guided by other answers

Explain the reason for Ambiguous column error in SQL Server 2008

Right I think I have found some explanation for this weird behaviour

IF you only do something like this

SELECT Column1, * FROM Table_Name  

this should work ok.

But when you do something like

SELECT Column1, * FROM Table_Name 
ORDER BY Column1  --<-- this Column1 is contained in `*` as well as in the SELECT
                     -- statement too, SQL Server needs to know which one to use
                     -- in your order by clause.

It will throw an error as Column1 is being SELECTED twice in you SELECT Query and SQL Server wants to know by which column you want to order by your Results .

Ambiguous column is in your Order by clause but not in your Select statement.

Further Explanation

Further to prove my point the following is the order in which SQL directives get executed.

1. FROM clause

2. WHERE clause

3. GROUP BY clause

4. HAVING clause

5. SELECT clause

6. ORDER BY clause

As you can see the SELECT operator is executed before the ORDER BY clause. therefore in your case SELECT clause will have two columns with same name, and when it comes to ORDER BY the results SQL Server want to know which column to use in your ORDER BY and it throws the error of Ambiguous column.

When used with alias the Ambiguity is resolved and you get no more errors.

SQL error Ambiguous column name

There probably isn't any reason to count a column name. Just count all the rows using *:

SELECT COUNT_BIG(*) AS [Antall ordre] 
FROM dbo.[3S Company A_S$Warehouse Activity Header] ah NNER JOIN
     dbo.[3S Company A_S$Sales Header] sh
     ON sh.[No_] = ah.[Source No_]  
WHERE ah.[Destination No_] = '" & strSelskab & "'  and
      ah.[No_ Printed] > 0

Notice the use of table aliases. This also makes the query easier to write and to read.

Ambiguous column name error in order by clause

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.

Ambiguous column name error

Because ARTIFACTTYPE can refer to either A.ARTIFACTTYPE or B.ARTIFACTTYPE and the server needs to know which one you want, just change it to A.ARTIFACTTYPE and you should be okay in this case.

To clarify, you need to specify the alias prefix any time the column name is ambiguous. It isn't bad practice to always use alias prefixes as it makes it clear which columns are coming from which tables when you read the query, and eliminates issues like this one.

One might wonder why you need to distinguish between which of two columns you want when they both refer to the same column in the same table. The answer is that when you join a table to itself, the values from A.column and B.column may be different depending on the join criteria (such as may be the case with an outer join where values in one of the columns may be null).

---

Related Topics