April 23, 2008 at 12:10 pm
Hi,
I've got an error below if I set to level 80 but if I set 90 it doesn't give me any error.
---------------------------------------------
"Server: Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'cwono'."
---------------------------------------------
Here's my query:
select distinct a.cwono , a.dorder, a.drequest, a.lhold, a.csono, a.ccustno
from miword a
left outer join miwips on a.cwono = miwips.cwono
where lvoid = 0 order by cwono desc
I know that it has something to do with the aliases but I need to know the difference between 80 and 90.
Please advice.
Thanks,
JanC
April 23, 2008 at 12:14 pm
Since you have 2 instances of that column, you need to tell SQL which one to order by ..
select distinct a.cwono , a.dorder, a.drequest, a.lhold, a.csono, a.ccustno
from miword a
left outer join miwips on a.cwono = miwips.cwono
where lvoid = 0 order by a.cwono desc
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 23, 2008 at 12:14 pm
The problem is the colum in the Order By. Needs the table alias on it.
The difference is that 80 (SQL 2000) can't order by a column alias, while 90 (SQL 2005) can. So a column used in the select statement can be used by 2005 in the Where, Order By, Group By and Having clauses, and it will know which one you mean. 2000 wasn't quite that clever.
If you want to see if the other way around, try selecting the same column twice, from the same table, without column aliases, and then use that same column in the Order By clause.
select Column1, Column1
from dbo.Table
order by Column1
2000 will run that query without an error, but 2005 will give an error on it. It's because of the same thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 23, 2008 at 12:35 pm
Thanks... I really appreciate your quick reponse on this 🙂 So, I think its safe that we explicitly put an alias on it so its backward compatible.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply