Ambiguous field name error

Question

Post reply

Ambiguous field name error

btio_3000

SSCrazy

Points: 2662
More actions
December 1, 2008 at 3:38 pm

#127301

Hi,
Here is the statement I am using (SQL 2005)
SELECT FieldA, ..., Name1, ..., Name1, ..., FieldZ
FROM dbo.Table1
WHERE ...
ORDER BY Name1
So there is a very simple SELECT where one of the fields - Name1 - is selected twice and then it's trying to sort by the same field.
There is quite a number of databases (on the same server) that are running that SELECT with no problems whatsoever, one newly created though generates an "Ambiguous field name" error
Anybody?
Thanks!

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Michelle Ufford-459603 SSCommitted Points: 1642 More actions · Answer 1

I think the obvious question here is: why are you selecting the same column more than one time?

That aside, you're getting the error because you're ordering by the duplicate column.

Try something like:

SELECT FieldA, ..., Name1, ..., Name1 As 'dupName1', ..., FieldZ

FROM dbo.Table1

WHERE ...

ORDER BY Name1

Regards,

Michelle Ufford
SQLFool.com - Adventures in SQL Tuning

btio_3000 SSCrazy Points: 2662 More actions · Answer 2

Thanks Michell,

I understand that, the question is rather why it was working for years (and still is for all other databases), but suddenly does not with this particular one.

Again, to fix that statement is of course not a big deal, but going through tons of code searching for all those typos is a different story.

From my experience, such kind of behavior happens when you transfer large amount af data from one database to another in 2005 and it's not doing constraints properly or something like that, but again, that never happened in a newly created database

Thanks again

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

When did you upgrade the database to SQL Server 2005 and compatibility mode 90?

I just did some testing on a SQL Server 2000 instance, a database in compatibility mode 80 on a SQL Server 2005, and the same database on SQL Server 2005 in compatibility mode 90.

This query

select jobname, jobdesc, jobname, jobdesc from dbo.Job order by jobname

Ran fine in the first two (SQL Server 2000 and SQL Server 2005 compatibility mode 80), and died in the third (SQL Server 2005 compatility mode 90) respectively.

btio_3000 SSCrazy Points: 2662 More actions · Answer 4

It's a compatability level, there you go.

Thanks a lot!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

The SQL 2005 parser is a lot stricter about what it considers valid SQL. Technically, that syntax isn't valid, but the 2000 parser would accept it, even though it could lead to really strange results.

Select Firstname as Name, LastName as Name, Email

FROM Person

Order by Name -- which one?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 6

The old parser would also let you refer to columns by the table alias and the new one does not.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Grant Fritchey (12/2/2008)
The old parser would also let you refer to columns by the table alias and the new one does not.

You mean like this, or something else?

select title, FirstName as name, LastName as name from Person.Person p

order by p.FirstName

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 8

Yep. I'm just starting the process of evaluating all our 2000 servers using the 2008 Upgrade Advisor. That's one syntax error that's popping up all over the place. I'm a little shocked that's not proper syntax actually.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

The query I posted runs fine on 2005 SP2 and 2008 RTM.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 10

Clarification: "Column aliases in ORDER BY clause cannot be prefixed by table alias"

Sorry. What I said earlier was misleading. Also, on rereading, I'm not shocked that this doesn't work. I'm shocked it used to.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

Gail, I took your simple query (changed the table to Contact in AdventureWorks) and ran the following:

select title, FirstName as name, LastName as name from Person.Contact p

order by p.FirstName -- This one ran okay

go

select title, FirstName as name, LastName as name from Person.Contact p

order by name -- This one failed with ambiguous colun name 'name'

go

select title, FirstName as fname, LastName as name from Person.Contact p

order by fname -- This one ran okay.

go

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Grant Fritchey (12/2/2008)
Clarification: "Column aliases in ORDER BY clause cannot be prefixed by table alias"
Sorry. What I said earlier was misleading. Also, on rereading, I'm not shocked that this doesn't work. I'm shocked it used to.

Ah, yeah, that makes a lot more sense.

There were a number of peculiarities with 2000's parser and the order by clause

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

If I remember correctly (and I just don't feel like testing it), you couldn't use a column alias in the order by in SQL Server 2000.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

You could. I had this nasty mess on my hands once on SQL 2000.

SELECT SomeColumn, CONVERT(varchar(20),SomeDate,107) AS SomeDate

FRom SomeTable

Order By SomeDate

The ordering came out with April first.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass