Ambiguous column name error

Question

When executing the following (complete) SQL query on Microsoft SQL Server 2000:

SELECT B.ARTIFACTTNS, B.ARTIFACTNAME, B.ARTIFACTTYPE, B.INITIALBYTES, B.TIMESTAMP1, B.FILENAME, B.BACKINGCLASS, 
       B.CHARENCODING, B.APPNAME, B.COMPONENTTNS, B.COMPONENTNAME, B.SCAMODULENAME, B.SCACOMPONENTNAME 
FROM (SELECT DISTINCT A.ARTIFACTTYPE, A.ARTIFACTTNS, A.ARTIFACTNAME 
      FROM (SELECT DISTINCT ARTIFACTTYPE, ARTIFACTTNS, ARTIFACTNAME 
            FROM CUSTPROPERTIES WHERE PNAME = 'AcmeSystemName' AND PVALUE = 'MyRuleGroup' 
                  UNION SELECT DISTINCT ARTIFACTTYPE, ARTIFACTTNS, ARTIFACTNAME 
                          FROM CUSTPROPERTIES WHERE PNAME = 'AcmeSystemDisplayName' AND PVALUE = 'MyRuleGroup') A, 
           (SELECT DISTINCT ARTIFACTTYPE, ARTIFACTTNS, ARTIFACTNAME 
            FROM CUSTPROPERTIES WHERE PNAME = 'AcmeSystemTargetNameSpace' AND PVALUE = 'http://MyModule') B 
WHERE A.ARTIFACTTYPE = B.ARTIFACTTYPE AND A.ARTIFACTTNS = B.ARTIFACTTNS AND A.ARTIFACTNAME = B.ARTIFACTNAME) A, BYTESTORE B 
    WHERE (A.ARTIFACTTYPE = 'BRG') AND A.ARTIFACTTYPE = B.ARTIFACTTYPE AND A.ARTIFACTTNS = B.ARTIFACTTNS AND A.ARTIFACTNAME = B.ARTIFACTNAME 
    ORDER BY ARTIFACTTYPE, ARTIFACTTNS, ARTIFACTNAME

I get the following exception:

java.sql.SQLException: [Acme][SQLServer JDBC Driver][SQLServer]
    Ambiguous column name 'ARTIFACTTYPE'.

What am I doing wrong here and how can I correct it?

Instalment

You can get rid of every distinct in your query. – Undine

Also, It looks like you've built a "One True Lookup Table", a data modeling design blunder so common it has its own name. Although I could be mistaken, the pattern looks Oh! so familiar. Google that term. – Undine

Answer 1

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).

Stepha

Because, per my own answer below, I don't see why anything in this query is ambiguous. In particular, I don't see any unqualified ARTIFACTTYPE where adding an A or B would make sense. Your explanation of what ambiguity means is just fine, but I suspect the OP understands all that. – Denim

Thanks Dave, after looking at the query more carefully it doesn't appear there is any ambiguity although I have used databases that are very picky about enforcing aliases, SQL Server might be one of them. In any case, regardless of the query posted, my answer clearly addresses the reported error. – Stepha

Hi Robert, I tried your suggestion, but now run into a different error. Could you please take another look at my updated question? Paul – Instalment

Answer 2

If that's the exact query you're running, I have no idea why it would find anything ambiguous.

I wrote what I think is an equivalent query and ran it in my database (Oracle) with no problem.

EDIT Adding exact output of a new experiment in Oracle. The query executed in this experiment is the exact query given by the OP, with the table name filled in. NO OTHER CHANGES. There's nothing ambiguous in this query. So, either that is not the exact query that is being executed, or SQL Server has a parser bug.

SQL> create table props (pname varchar2(100),
  2                       pvalue varchar2(100),
  3                       artifacttype number,
  4                       artifacttns number,
  5                       artifactname number);

Table created.

SQL> SELECT      
  2    DISTINCT A.ARTIFACTTYPE, A.ARTIFACTTNS, A.ARTIFACTNAME
  3  FROM
  4   (SELECT DISTINCT 
  5      ARTIFACTTYPE, 
  6      ARTIFACTTNS, 
  7      ARTIFACTNAME 
  8    FROM props 
  9    WHERE PNAME = 'AcmeSystemName' 
 10        AND PVALUE = 'MyRuleGroup' 
 11    UNION 
 12    SELECT DISTINCT 
 13      ARTIFACTTYPE, 
 14      ARTIFACTTNS, 
 15      ARTIFACTNAME 
 16    FROM props
 17    WHERE PNAME = 'AcmeSystemDisplayName' 
 18        AND PVALUE = 'MyRuleGroup') A, 
 19  (SELECT DISTINCT 
 20      ARTIFACTTYPE, 
 21      ARTIFACTTNS, 
 22      ARTIFACTNAME 
 23   FROM props 
 24   WHERE PNAME = 'AcmeSystemTargetNameSpace' 
 25      AND PVALUE = 'http://mymodule') B
 26  WHERE A.ARTIFACTTYPE = B.ARTIFACTTYPE 
 27      AND A.ARTIFACTTNS = B.ARTIFACTTNS 
 28      AND A.ARTIFACTNAME = B.ARTIFACTNAME
 29  /

no rows selected

End Edit

My suggestion for getting around the error is to give the table in each select clause a unique alias and qualify all column references. Like this:

SELECT
  DISTINCT A.ARTIFACTTYPE, A.ARTIFACTTNS, A.ARTIFACTNAME
FROM
 (SELECT DISTINCT 
    P1.ARTIFACTTYPE, 
    P1.ARTIFACTTNS, 
    P1.ARTIFACTNAME 
  FROM {PROPERTIES_TABLE_NAME} P1
  WHERE PNAME = 'AcmeSystemName' 
      AND PVALUE = 'MyRuleGroup' 
  UNION 
  SELECT DISTINCT 
    P2.ARTIFACTTYPE, 
    P2.ARTIFACTTNS, 
    P2.ARTIFACTNAME 
  FROM {PROPERTIES_TABLE_NAME} P2
  WHERE PNAME = 'AcmeSystemDisplayName' 
      AND PVALUE = 'MyRuleGroup') A, 
(SELECT DISTINCT 
    P3.ARTIFACTTYPE, 
    P3.ARTIFACTTNS, 
    P3.ARTIFACTNAME 
 FROM {PROPERTIES_TABLE_NAME} P3
 WHERE PNAME = 'AcmeSystemTargetNameSpace' 
    AND PVALUE = 'http://mymodule') B
WHERE A.ARTIFACTTYPE = B.ARTIFACTTYPE 
    AND A.ARTIFACTTNS = B.ARTIFACTTNS 
    AND A.ARTIFACTNAME = B.ARTIFACTNAME

Denim

You used alias names where the OP did not - that's why it's correct in your version - you actually fixed the problem. – Tumbler

Sean: The query I wrote above is my suggestion to the OP on how to get around his problem. It's not what I ran in my database. Two separate thoughts. – Denim

Added a concrete example showing that I can run the OP's EXACT query in Oracle without getting an ambiguity error. – Denim

Answer 3

Are you listing the complete query? Perhaps you have also ORDER BY clause - that could cause that problem

I would support Dave on that that there should be no problem with the posted query

Vaccaro

Answer 4

To be clear, it is lines 13, 14 and 15 that have the ambiguous columns.

Pasha

Why? Why would those lines be ambiguous but lines 5, 6, and 7 not be? – Denim

I thought the same initially, but after looking closer at the code I would support Dave on that – Vaccaro

Not sure why those lines are ambiguous. Just telling you what mgmt studio is saying. The 8 year old db server might have something to do with it. I take it you guys have tried this on sql server 2000 as the original poster specified. Thanks for the down votes without even trying it on sql 2000. – Pasha

I tried your suggestion, but now run into a different error. Could you please take another look at my updated question? Paul – Instalment

Answer 5

You need to specify the tables in the ORDER BY clause, like this:

ORDER BY A.ARTIFACTTYPE, A.ARTIFACTTNS, A.ARTIFACTNAME

Instalment