|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
2005, Ambiguous column name, and ORDER BY"Ambiguous column name" error. SELECT client_status, client_status FROM ClientStatus ORDER BY client_status I have to change it to: SELECT client_status, client_status FROM ClientStatus ORDER BY ClientStatus.client_status I obviously wouldn't write a query like this, but it is getting generated dynamically throughout our site. Is there any magic settings that would allow this to not error out. Our 2005 upgrade has gone smoothly til this. Or am I looking at changing all the application code to check for this possibility. Thanks, Dave As per the doumentation
http://msdn2.microsoft.com/en-us/library/ms178653.aspx, this is a change in SQL 2005. Refer the example at the end of the dcoument. It looks like it is time to change your code. -- Show quoteTHANKS & PLEASE RATE THE POSTING. --RAVI-- "David D Webb" wrote: > The following is legal in 2000, but not 2005 (90 compat mode) - generates > "Ambiguous column name" error. > > SELECT client_status, client_status FROM ClientStatus ORDER BY client_status > > I have to change it to: > > SELECT client_status, client_status FROM ClientStatus ORDER BY > ClientStatus.client_status > > I obviously wouldn't write a query like this, but it is getting generated > dynamically throughout our site. > > Is there any magic settings that would allow this to not error out. Our > 2005 upgrade has gone smoothly til this. Or am I looking at changing all > the application code to check for this possibility. > > Thanks, > Dave > > > Thanks Ravi, the implications weren't apparent to me when I originally read
that. Well, considering they are the exact same column in my case, the sort order is not ambiguous, so that 9.0 change is just creating a lot of work for nothing. I can understand the example where two different columns are aliased with the same name, then ordered on, but why would you ever do that. The query processor should be smart enough to know the difference between the two scenarios. Show quote "Ravi" <ravishank***@hotmail.com> wrote in message news:D369F1A7-108C-4BC9-9668-76C037BDD7A9@microsoft.com... > As per the doumentation > http://msdn2.microsoft.com/en-us/library/ms178653.aspx, this is a change > in > SQL 2005. Refer the example at the end of the dcoument. > > It looks like it is time to change your code. > -- > THANKS & PLEASE RATE THE POSTING. > --RAVI-- > > > "David D Webb" wrote: > >> The following is legal in 2000, but not 2005 (90 compat mode) - generates >> "Ambiguous column name" error. >> >> SELECT client_status, client_status FROM ClientStatus ORDER BY >> client_status >> >> I have to change it to: >> >> SELECT client_status, client_status FROM ClientStatus ORDER BY >> ClientStatus.client_status >> >> I obviously wouldn't write a query like this, but it is getting generated >> dynamically throughout our site. >> >> Is there any magic settings that would allow this to not error out. Our >> 2005 upgrade has gone smoothly til this. Or am I looking at changing all >> the application code to check for this possibility. >> >> Thanks, >> Dave >> >> >> David D Webb wrote:
> Thanks Ravi, the implications weren't apparent to me when I originally read I guess I'd have to ask, why would you return the same column twice with > that. > > Well, considering they are the exact same column in my case, the sort order > is not ambiguous, so that 9.0 change is just creating a lot of work for > nothing. I can understand the example where two different columns are > aliased with the same name, then ordered on, but why would you ever do that. > The query processor should be smart enough to know the difference between > the two scenarios. > the same name, as you're doing? David,
It might be harder than you think for SQL Server to figure out when the column name is ambiguous and when it is not. For example, in .... FROM T1 LEFT OUTER JOIN T2 ON T1.a = T2.a ORDER BY a, b it is ambiguous, but in .... FROM T1 INNER JOIN T2 ON T1.a = T2.a ORDER BY a, b it is not. And perhaps more importantly, if the query processor were to be responsible for this check, it would mean the ORDER BY clause couldn't always be validated at parsing time, because the query would sometimes have to be interpreted before the ORDER BY items could be validated. It's unfortunate that in SQL Server 2000 and earlier, alias resolution was buggy, but it's worth the trouble to fix things up and get into the habit of being more precise. Steve Kass Drew University www.stevekass.com David D Webb wrote: Show quote >Thanks Ravi, the implications weren't apparent to me when I originally read >that. > >Well, considering they are the exact same column in my case, the sort order >is not ambiguous, so that 9.0 change is just creating a lot of work for >nothing. I can understand the example where two different columns are >aliased with the same name, then ordered on, but why would you ever do that. >The query processor should be smart enough to know the difference between >the two scenarios. > > >"Ravi" <ravishank***@hotmail.com> wrote in message >news:D369F1A7-108C-4BC9-9668-76C037BDD7A9@microsoft.com... > > >>As per the doumentation >>http://msdn2.microsoft.com/en-us/library/ms178653.aspx, this is a change >>in >>SQL 2005. Refer the example at the end of the dcoument. >> >>It looks like it is time to change your code. >>-- >>THANKS & PLEASE RATE THE POSTING. >>--RAVI-- >> >> >>"David D Webb" wrote: >> >> >> >>>The following is legal in 2000, but not 2005 (90 compat mode) - generates >>>"Ambiguous column name" error. >>> >>>SELECT client_status, client_status FROM ClientStatus ORDER BY >>>client_status >>> >>>I have to change it to: >>> >>>SELECT client_status, client_status FROM ClientStatus ORDER BY >>>ClientStatus.client_status >>> >>>I obviously wouldn't write a query like this, but it is getting generated >>>dynamically throughout our site. >>> >>>Is there any magic settings that would allow this to not error out. Our >>>2005 upgrade has gone smoothly til this. Or am I looking at changing all >>>the application code to check for this possibility. >>> >>>Thanks, >>>Dave >>> >>> >>> >>> >>> > > > > Its not a matter of being more precise, like I said, in my case this is
dynamically generated SQL based on user settings. My complaint is that they have chosen to error out 100% of the time on a situation that may NOT be ambiguous. As the programmer, I would like to be able to over-ride that when I know in my situation it is NOT ambiguous (or in the least case, I don't care). We are only talking about a sort order here - not life and death to all situations, where as errors are. SQL Server in general lacks compilation warnings, like a standard language compiler may have. This is one instance I would like to see a warning, not an error. Try justifying the hours and cost to fix an ambiguous sort order... Show quote "Steve Kass" <sk***@drew.edu> wrote in message news:%23pSqDQDvGHA.5084@TK2MSFTNGP04.phx.gbl... > David, > > It might be harder than you think for SQL Server to figure out > when the column name is ambiguous and when it is not. For > example, in > > ... > FROM T1 LEFT OUTER JOIN T2 > ON T1.a = T2.a > ORDER BY a, b > > it is ambiguous, but in > > ... > FROM T1 INNER JOIN T2 > ON T1.a = T2.a > ORDER BY a, b > > it is not. And perhaps more importantly, if the query > processor were to be responsible for this check, it > would mean the ORDER BY clause couldn't always be > validated at parsing time, because the query would sometimes > have to be interpreted before the ORDER BY items > could be validated. > > It's unfortunate that in SQL Server 2000 and earlier, > alias resolution was buggy, but it's worth the trouble to fix > things up and get into the habit of being more precise. > > Steve Kass > Drew University > www.stevekass.com > > > David D Webb wrote: > >>Thanks Ravi, the implications weren't apparent to me when I originally >>read that. >> >>Well, considering they are the exact same column in my case, the sort >>order is not ambiguous, so that 9.0 change is just creating a lot of work >>for nothing. I can understand the example where two different columns are >>aliased with the same name, then ordered on, but why would you ever do >>that. The query processor should be smart enough to know the difference >>between the two scenarios. >> >> >>"Ravi" <ravishank***@hotmail.com> wrote in message >>news:D369F1A7-108C-4BC9-9668-76C037BDD7A9@microsoft.com... >> >>>As per the doumentation >>>http://msdn2.microsoft.com/en-us/library/ms178653.aspx, this is a change >>>in >>>SQL 2005. Refer the example at the end of the dcoument. >>> >>>It looks like it is time to change your code. >>>-- >>>THANKS & PLEASE RATE THE POSTING. >>>--RAVI-- >>> >>> >>>"David D Webb" wrote: >>> >>> >>>>The following is legal in 2000, but not 2005 (90 compat mode) - >>>>generates >>>>"Ambiguous column name" error. >>>> >>>>SELECT client_status, client_status FROM ClientStatus ORDER BY >>>>client_status >>>> >>>>I have to change it to: >>>> >>>>SELECT client_status, client_status FROM ClientStatus ORDER BY >>>>ClientStatus.client_status >>>> >>>>I obviously wouldn't write a query like this, but it is getting >>>>generated >>>>dynamically throughout our site. >>>> >>>>Is there any magic settings that would allow this to not error out. Our >>>>2005 upgrade has gone smoothly til this. Or am I looking at changing >>>>all >>>>the application code to check for this possibility. >>>> >>>>Thanks, >>>>Dave >>>> >>>> >>>> >>>> >> >> >> "David D Webb" <spivey@nospam.post.com> wrote in message Dynamic SQL has its own set of problems, but for the question at hand,news:uXEiMOCwGHA.1224@TK2MSFTNGP03.phx.gbl... > Its not a matter of being more precise, like I said, in my case this is > dynamically generated SQL based on user settings. My complaint is that > they have chosen to error out 100% of the time on a situation that may NOT > be ambiguous. As the programmer, I would like to be able to over-ride > that when I know in my situation it is NOT ambiguous (or in the least > case, I don't care). We are only talking about a sort order here - not > life and death to all situations, where as errors are. SQL Server in > general lacks compilation warnings, like a standard language compiler may > have. This is one instance I would like to see a warning, not an error. > Try justifying the hours and cost to fix an ambiguous sort order... > yes, I'd justify the work to prevent this from changing meaning if a join were changed to OUTER, or if the join condition were changed to <=, for example (which could happen if this could is found and believed to be a good model for the new question.. Regardless of the fact that a.col and b.col are identical in the specific queries where you refer to them as col for ordering, I think this is dangerous, and probably in violation of the ANI SQL standard. Show quote > > > "Steve Kass" <sk***@drew.edu> wrote in message > news:%23pSqDQDvGHA.5084@TK2MSFTNGP04.phx.gbl... >> David, >> >> It might be harder than you think for SQL Server to figure out >> when the column name is ambiguous and when it is not. For >> example, in >> >> ... >> FROM T1 LEFT OUTER JOIN T2 >> ON T1.a = T2.a >> ORDER BY a, b >> >> it is ambiguous, but in >> >> ... >> FROM T1 INNER JOIN T2 >> ON T1.a = T2.a >> ORDER BY a, b >> >> it is not. And perhaps more importantly, if the query >> processor were to be responsible for this check, it >> would mean the ORDER BY clause couldn't always be >> validated at parsing time, because the query would sometimes >> have to be interpreted before the ORDER BY items >> could be validated. >> >> It's unfortunate that in SQL Server 2000 and earlier, >> alias resolution was buggy, but it's worth the trouble to fix >> things up and get into the habit of being more precise. >> >> Steve Kass >> Drew University >> www.stevekass.com >> >> >> David D Webb wrote: >> >>>Thanks Ravi, the implications weren't apparent to me when I originally >>>read that. >>> >>>Well, considering they are the exact same column in my case, the sort >>>order is not ambiguous, so that 9.0 change is just creating a lot of work >>>for nothing. I can understand the example where two different columns >>>are aliased with the same name, then ordered on, but why would you ever >>>do that. The query processor should be smart enough to know the >>>difference between the two scenarios. >>> >>> >>>"Ravi" <ravishank***@hotmail.com> wrote in message >>>news:D369F1A7-108C-4BC9-9668-76C037BDD7A9@microsoft.com... >>> >>>>As per the doumentation >>>>http://msdn2.microsoft.com/en-us/library/ms178653.aspx, this is a change >>>>in >>>>SQL 2005. Refer the example at the end of the dcoument. >>>> >>>>It looks like it is time to change your code. >>>>-- >>>>THANKS & PLEASE RATE THE POSTING. >>>>--RAVI-- >>>> >>>> >>>>"David D Webb" wrote: >>>> >>>> >>>>>The following is legal in 2000, but not 2005 (90 compat mode) - >>>>>generates >>>>>"Ambiguous column name" error. >>>>> >>>>>SELECT client_status, client_status FROM ClientStatus ORDER BY >>>>>client_status >>>>> >>>>>I have to change it to: >>>>> >>>>>SELECT client_status, client_status FROM ClientStatus ORDER BY >>>>>ClientStatus.client_status >>>>> >>>>>I obviously wouldn't write a query like this, but it is getting >>>>>generated >>>>>dynamically throughout our site. >>>>> >>>>>Is there any magic settings that would allow this to not error out. >>>>>Our >>>>>2005 upgrade has gone smoothly til this. Or am I looking at changing >>>>>all >>>>>the application code to check for this possibility. >>>>> >>>>>Thanks, >>>>>Dave >>>>> >>>>> >>>>> >>>>> >>> >>> >>> > >  
Other interesting topics
|
|||||||||||||||||||||||
