No this is the way SQL works. Basicly the select part of the query is done after the where filter is applied. So you can't use an alias created in the select part in the where part of the query. You have to do it like in your Workaround example.
/Andreas On Thu, Apr 28, 2011 at 7:55 AM, mb <michal.bergm...@gmail.com> wrote: > I think this is a feature, not a bug. Does any other RDBMS allow using > aliases in where clause? Oracle 11 doesn't. > > It depends on query optimizer whether distanceEarth is calculated > twice or not. If user function is deterministic and query optimizer > it's smart enough, it should be able to figure out that distanceEarth > is called twice with the same arguments and use result of first call > when distanceEarth is called second time. > > M. > > > On Apr 27, 10:35 pm, "bedla.czech" <bedla.cz...@gmail.com> wrote: >> Hi, >> I want to use calculated column alias in where clause. While I use it >> in order by clause, everything is ok. >> >> Error: >> select *, distanceEarth(lat, lon, 50.245284, 16.163576) as dist >> from location >> where dist < 50 >> order by dist >> >> Workaround: >> select *, distanceEarth(lat, lon, 50.245284, 16.163576) as dist >> from location >> where distanceEarth(lat, lon, 50.245284, 16.163576) < 50 >> order by dist >> >> Note: distanceEarth is custom function alias. >> >> In workaround you can see that I use calculation two times: first for >> order by clause and second for where clause. >> >> Am I missing something? >> >> Thanks for response >> Ivos >> >> Ps.: I originaly posted this question at h2 bug tracker, sorry for >> that. > > -- > You received this message because you are subscribed to the Google Groups "H2 > Database" group. > To post to this group, send email to h2-database@googlegroups.com. > To unsubscribe from this group, send email to > h2-database+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/h2-database?hl=en. > > -- Andreas Henningsson "Vanligt sunt förnuft är inte särkilt vanligt." -- Voltaire -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to h2-database@googlegroups.com. To unsubscribe from this group, send email to h2-database+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
