Ambiguous column name error
-
We have a vendor whose product tries to run a query and receives an error:
"Ambiguous Column Name 'Location'"
Their query:
SELECT Assets.DataCategory, Assets.AssetNumber, Assets.Location, Locations.LocationDescription, Assets.DescriptionID, Assets.ConditionCode, ConditionCodes.ConditionDescription, Assets.ScanDate, Assets.Quantity, Assets.UnitCost, Assets.CostCenter, Assets.Field2, Assets.ProductInventory
FROM Assets, DescriptionCatalog, ConditionCodes, Locations
WHERE Assets.DescriptionID = DescriptionCatalog.DescriptionID AND Assets.ConditionCode = ConditionCodes.ConditionCode AND Assets.Location = Locations.Location AND Assets.DataCategory = 'EQ' AND Assets.AssetNumber IN ('D081067')
ORDER BY AssetNumber, Location
I know the error is coming from the ORDER BY because Location isn't qualified with a table name and SQL Server doesn't know if it should order by Assets.Location or Locations.Location
The response I get from the vendor is:
"SQL Server is supposed to resolve ambiguous field references on its own. So I'm somewhat concerned that your environment isn't resolving the field references."
They seem to think that there is something in my SQL 2000 environment that isn't set up properly. I've never heard of this magical "ambiguous reference resolver" but I don't claim to know everything about SQL Server.
Books online states:
The tables and views specified in the FROM clause may have duplicate column names. It is especially likely that foreign keys will have the same column name as their related primary key. To resolve the ambiguity between duplicate names, the column name must be qualified with the table or view name:
SELECT DISTINCT Customers.CustomerID, Customers.CompanyNameFROM Customers JOIN Orders ON ( Customers.CustomerID = Orders.CustomerID)WHERE Orders.ShippedDate > 'May 1 1998'Is there something I'm missing?
Thanks
-
Perhaps SQL 2005 would be happy assuming that the intent is to use Assets.Location since that is what is referenced in the field list (I don't have enough experience with SQL2K5 to be more confident in saying that), but 2000 won't make that assumption. The Order By needs to be fully qualified if it's ambiguous. I've never heard of any configuration switch or add-in for SQL2K that automatically resolves duplicate field name ambiguity.
Maybe the vendor's product is available for multiple database platforms and they're confusing features between different database vendors. It's not your installation, it's their buggy query.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
This query would fail in SQL Server 2005 as well for the same reason it is failing in SQL Server 2000.
-
I just tried it in 2005 and it actually does work. Our production server is 2000 though so they need to make it work there.
-
Huh. Learn something new every day.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
Guess I'll have to look at this more. I have had queries fail for this very reason, so I wonder if there is something else I may be missing.
-
Just from the practical point for ease of troubleshooting, here's from our "standards" book at work...
All column names shall be prefixed with a table alias in the presence of joins even if the column names are unique across all tables to facilitate ease of troubleshooting.
It's a good practice and then you don't have to worry about SQL Server resolving it. I'd tell the vendor to add the table aliases... and then I'd start looking for another vendor or write the code myself.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I'll second Jeff on this, and I'd even take it further. It may be a good idea to alias columns even in single table queries. You never know when a requirement may change and what was once a single table query suddenly has two or more tables. If you have already aliased the existing columns and table in the query, you don't have to make a lot of possible changes. Just a thought.
-
When you go back to the vendor, ask them to specify what database and version they are citing for the resolution of ambiguous references. Especially since the reference they gave you was ambiguous and your database (i.e., you) could not resolve the ambiguity.
They may be thinking that you are on a different version than you are.
Also, you may want to mention to them that you've contacted public support newsgroups regarding this question and, that if it is not resolved and soon, you will be advertising their name and making public their lack of quality in code and their lack of internal standards in development. IF you want to play hardball with them. You may want to check with your management before taking this step, just to make sure you don't have adverse fallout dropping your way.
Our bosses make us accountable. Why don't theirs?
-
>>> Our bosses make us accountable. Why don't theirs? <<<
theirs might but is a tough business decision to "fix" bugs beacuse the time spent on that is very unproductive
Just common business scenario. Abd if you find someone that gives you such level of support you should definitely consider switching vendors
* Noel -
Hmmm... I did some testing and found some interesting behavior. My conclusion is...
In 2000, the ORDER BY field is verified against all fields in all tables in the FROM clause. It ignores field aliases. If found more than once, it is ambiguous.
In 2005, the ORDER BY field is verified first against the SELECT clause aliases and field names. If not found in the SELECT clause, it then verifies against all fields in all tables in the FROM clause.
-- Works in 2005, Fails in 2000
select
HisLocation = x.Location, y.Location from x join y.... order by Location
-- Works in 2005, Fails in 2000
select
x.Location from x join y.... order by Location
-- Fails in 2005, Fails in 2000
select
x.Location, y.Location from x join y.... order by Location
-- Fails in 2005, Fails in 2000
select
HisLocation = x.Location, HerLocation = y.Location from x join y.... order by Location
Regards,
Rubes -
Okay, now that's just weird. I had never noticed this behavior from SQL Server 2005, but you are absolutely correct. When I only specific one column in the SELECT (out of duplicates), it orders fine without complaint. If I specify both, it complains of an error.
Of course, in SQL 2005 it also orders by alias names. Which I previously knew. If you change the name of one of the columns to "Pansy" you can do an "order by Pansy" command. So, I guess in that context, this behavior actually makes sense.
It's also pretty cool. @=)
-
Thanks to everyone who replied. I didn't know this would spark such a discussion.
The vendor is going to change their code (they have to if they expect it to work in SQL2000).
My next challenge: Their install adds BUILTIN\users to my logins and gives it dbo to the database.
-
just my 2 ct
With SQL2005 the order by in will use the column-aliasses whenever possible.
That's why your query did not fail with sql2005 (you only specified location once in your select column list).
If I remember well, SQLUA (upgrade advisor) even produces a warning if you prefix your columns in an order by clause.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I haven't used SQLUA, but I am wondering why it would issue a warnig FOR prefixing your columns in an order by clause? By prefixing column names with a table alias you are eliminating any ambiguity in your code.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply