Thread: syntax for access an alias in the where clause ?
Hi ! It's long ago since I used SQL :-) select [expression 1] as [alias 1], [expression 2] as [alias 2] where [alias 1] < [alias 2] order by [alias 2] - [alias 1] ---> ERROR: Attribute '[alias 1]' not found I found a lot of 'select [expression] as' but I've never seen somebody accessing the alias ... Can't this be done ? M.
Is it allowed to use spaces in postgresql alias? Why dont you try to use alias1 instead of "alias 1"? On Wed, 2002-06-19 at 14:03, Michael Agbaglo wrote: > Hi ! > > It's long ago since I used SQL :-) > > select [expression 1] as [alias 1], [expression 2] as [alias 2] > where [alias 1] < [alias 2] > order by [alias 2] - [alias 1] > > ---> ERROR: Attribute '[alias 1]' not found > > I found a lot of 'select [expression] as' but I've never seen somebody > accessing the alias ... Can't this be done ? > > M. > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > -- Wei Weng Network Software Engineer KenCast Inc.
Wei Weng wrote: > Is it allowed to use spaces in postgresql alias? > > Why dont you try to use alias1 instead of "alias 1"? really amusing... muhahaha... I don't use the brackets either... I didn't assume that you might be interested in how the expression looks like or what aliases I'm using... > > > On Wed, 2002-06-19 at 14:03, Michael Agbaglo wrote: > >>Hi ! >> >>It's long ago since I used SQL :-) >> >>select [expression 1] as [alias 1], [expression 2] as [alias 2] >>where [alias 1] < [alias 2] >>order by [alias 2] - [alias 1] >> >>---> ERROR: Attribute '[alias 1]' not found >> >>I found a lot of 'select [expression] as' but I've never seen somebody >>accessing the alias ... Can't this be done ? >> >>M. >> >> >> >> >>---------------------------(end of broadcast)--------------------------- >>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >> >>
Michael Agbaglo <byteshifter@shifted-bytes.de> writes:
> select [expression 1] as [alias 1], [expression 2] as [alias 2]
> where [alias 1] < [alias 2]
> order by [alias 2] - [alias 1]
> ---> ERROR: Attribute '[alias 1]' not found
> I found a lot of 'select [expression] as' but I've never seen somebody
> accessing the alias ... Can't this be done ?
No, it can't, and the reason is that the WHERE clause is logically
evaluated before the SELECT list is (in fact, the SELECT list will
never be evaluated at rows where the WHERE clause returns false).
It would be circular to refer to SELECT outputs in WHERE.
regards, tom lane
The WHERE clause is evaluated before the SELECT list, at which point the value of "[alias 1]" is not known. You will need to use "[expression 1]" there instead. ORDER BY is evaluated after the SELECT, so "[alias 1]" can be used there with no problems. --- Michael Agbaglo <byteshifter@shifted-bytes.de> wrote: > Hi ! > > It's long ago since I used SQL :-) > > select [expression 1] as [alias 1], [expression 2] > as [alias 2] > where [alias 1] < [alias 2] > order by [alias 2] - [alias 1] > > ---> ERROR: Attribute '[alias 1]' not found > > I found a lot of 'select [expression] as' but I've > never seen somebody > accessing the alias ... Can't this be done ? > > M. > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org __________________________________________________ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com
On Wed, 19 Jun 2002, Michael Agbaglo wrote: > Hi ! > > It's long ago since I used SQL :-) > > select [expression 1] as [alias 1], [expression 2] as [alias 2] > where [alias 1] < [alias 2] > order by [alias 2] - [alias 1] > > ---> ERROR: Attribute '[alias 1]' not found > > I found a lot of 'select [expression] as' but I've never seen somebody > accessing the alias ... Can't this be done ? At least in postgres, no, because the where clause is processed before the select list so the alias doesn't have meaning. I believe the SQL spec says no as well.
On Wed, Jun 19, 2002 at 11:13:31PM +0200, Michael Agbaglo wrote: > > > Wei Weng wrote: > > >Is it allowed to use spaces in postgresql alias? > > > >Why dont you try to use alias1 instead of "alias 1"? > > > > really amusing... muhahaha... I'm sure he didn't mean to make a joke, nor ridicule you, it was just a misundertanding. > I don't use the brackets either... I didn't assume that you might be > interested in how the expression looks like or what aliases I'm using... Sometimes, it makes all the difference. In this case, it doesn't. Aliases are not available in the 'order by' clause: you need to repeat te full expressions there. Ross > > > > > > > >On Wed, 2002-06-19 at 14:03, Michael Agbaglo wrote: > > > >>Hi ! > >> > >>It's long ago since I used SQL :-) > >> > >>select [expression 1] as [alias 1], [expression 2] as [alias 2] > >>where [alias 1] < [alias 2] > >>order by [alias 2] - [alias 1] > >> > >>---> ERROR: Attribute '[alias 1]' not found > >> > >>I found a lot of 'select [expression] as' but I've never seen somebody > >>accessing the alias ... Can't this be done ? > >> > >>M. > >> > >> > >> > >> > >>---------------------------(end of broadcast)--------------------------- > >>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > >> > >> > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org