pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsyntax for access an alias in the where clause ?
7+ messages / 6 participants
[nested] [flat]
* syntax for access an alias in the where clause ?
@ 2002-06-19 18:03 Michael Agbaglo <byteshifter@shifted-bytes.de>
0 siblings, 4 replies; 7+ messages in thread
From: Michael Agbaglo @ 2002-06-19 18:03 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: syntax for access an alias in the where clause ?
@ 2002-06-19 19:56 Wei Weng <wweng@kencast.com>
parent: Michael Agbaglo <byteshifter@shifted-bytes.de>
3 siblings, 1 reply; 7+ messages in thread
From: Wei Weng @ 2002-06-19 19:56 UTC (permalink / raw)
To: Michael Agbaglo <byteshifter@shifted-bytes.de>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: syntax for access an alias in the where clause ?
@ 2002-06-19 21:13 Michael Agbaglo <byteshifter@shifted-bytes.de>
parent: Wei Weng <wweng@kencast.com>
0 siblings, 1 reply; 7+ messages in thread
From: Michael Agbaglo @ 2002-06-19 21:13 UTC (permalink / raw)
To: Wei Weng <wweng@kencast.com>; +Cc: pgsql-sql
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
>>
>>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: syntax for access an alias in the where clause ?
@ 2002-06-19 21:23 Tom Lane <tgl@sss.pgh.pa.us>
parent: Michael Agbaglo <byteshifter@shifted-bytes.de>
3 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2002-06-19 21:23 UTC (permalink / raw)
To: Michael Agbaglo <byteshifter@shifted-bytes.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: syntax for access an alias in the where clause ?
@ 2002-06-19 22:04 Jeff Eckermann <jeff_eckermann@yahoo.com>
parent: Michael Agbaglo <byteshifter@shifted-bytes.de>
3 siblings, 0 replies; 7+ messages in thread
From: Jeff Eckermann @ 2002-06-19 22:04 UTC (permalink / raw)
To: Michael Agbaglo <byteshifter@shifted-bytes.de>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: syntax for access an alias in the where clause ?
@ 2002-06-19 22:11 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Michael Agbaglo <byteshifter@shifted-bytes.de>
3 siblings, 0 replies; 7+ messages in thread
From: Stephan Szabo @ 2002-06-19 22:11 UTC (permalink / raw)
To: Michael Agbaglo <byteshifter@shifted-bytes.de>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: syntax for access an alias in the where clause ?
@ 2002-06-19 23:56 Ross J. Reedstrom <reedstrm@rice.edu>
parent: Michael Agbaglo <byteshifter@shifted-bytes.de>
0 siblings, 0 replies; 7+ messages in thread
From: Ross J. Reedstrom @ 2002-06-19 23:56 UTC (permalink / raw)
To: Michael Agbaglo <byteshifter@shifted-bytes.de>; +Cc: Wei Weng <wweng@kencast.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2002-06-19 23:56 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-06-19 18:03 syntax for access an alias in the where clause ? Michael Agbaglo <byteshifter@shifted-bytes.de>
2002-06-19 19:56 ` Wei Weng <wweng@kencast.com>
2002-06-19 21:13 ` Michael Agbaglo <byteshifter@shifted-bytes.de>
2002-06-19 23:56 ` Ross J. Reedstrom <reedstrm@rice.edu>
2002-06-19 21:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-06-19 22:04 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
2002-06-19 22:11 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox