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

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.ARTIFACTNAMEFROM (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') BWHERE A.ARTIFACTTYPE = B.ARTIFACTTYPE     AND A.ARTIFACTTNS = B.ARTIFACTTNS     AND A.ARTIFACTNAME = B.ARTIFACTNAME

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