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.

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

– Robert Gamble Nov 25 '08 at 21:04 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.

– Dave Costa Nov 26 '08 at 13:31 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. – Robert Gamble Nov 26 '08 at 16:24 I tried your suggestion, but now run into a different error.

Could you please take another look at my updated question? Paul – Paul Reiners Dec 10 '08 at 19:34.

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.

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.

You used alias names where the OP did not - that's why it's correct in your version - you actually fixed the problem. – Sean Carpenter Nov 25 '08 at 18:00 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. – Dave Costa Nov 25 '08 at 20:46 Added a concrete example showing that I can run the OP's EXACT query in Oracle without getting an ambiguity error. – Dave Costa Nov 25 '08 at 20:56.

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

– Dave Costa Nov 25 '08 at 17:49 I thought the same initially, but after looking closer at the code I would support Dave on that – kristof Nov 25 '08 at 18:19 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. – Logicalmind Nov 25 '08 at 21:53 I tried your suggestion, but now run into a different error.

Could you please take another look at my updated question? Paul – Paul Reiners Nov 25 '08 at 19:35.

You need to specify the tables in the ORDER BY clause, like this: ORDER BY A. ARTIFACTTYPE, A. ARTIFACTTNS, A.ARTIFACTNAME.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.