When will I be able to do something seemingly so basic as this re-use of an
alias?

SELECT DATE_ADD('2007-10-23', INTERVAL user_access_hours HOUR)
AS group_duration_date,
UNIX_TIMESTAMP(group_duration_date)
AS group_duration_date_timestamp
FROM end_user_groups;

Error Code : 1054
Unknown column 'group_duration_date' in 'field list'
(0 ms taken)

So instead I must do this very cumbersome and inefficient way:

SELECT DATE_ADD('2007-10-23', INTERVAL user_access_hours HOUR)
AS group_duration_date,
UNIX_TIMESTAMP(DATE_ADD('2007-10-23',
INTERVAL user_access_hours HOUR))
AS group_duration_date_timestamp
FROM end_user_groups;

*sigh*

This has been a cause of frustration since mysql 3.x series.
Are there any plans to fix this annoyance? If so, in what version?

Currently using:
mysql Ver 14.12 Distrib 5.0.41, for pc-linux-gnu (i686)

>When will I be able to do something seemingly
>so basic as this re-use of an alias?

Do you know an implementation of SQL which allows this?

PB

Daevid Vincent wrote:
> When will I be able to do something seemingly so basic as this re-use of an
> alias?
>
> SELECT DATE_ADD('2007-10-23', INTERVAL user_access_hours HOUR)
> AS group_duration_date,
> UNIX_TIMESTAMP(group_duration_date)
> AS group_duration_date_timestamp
> FROM end_user_groups;
>
> Error Code : 1054
> Unknown column 'group_duration_date' in 'field list'
> (0 ms taken)
>
> So instead I must do this very cumbersome and inefficient way:
>
> SELECT DATE_ADD('2007-10-23', INTERVAL user_access_hours HOUR)
> AS group_duration_date,
> UNIX_TIMESTAMP(DATE_ADD('2007-10-23',
> INTERVAL user_access_hours HOUR))
> AS group_duration_date_timestamp
> FROM end_user_groups;
>
> *sigh*
>
> This has been a cause of frustration since mysql 3.x series.
> Are there any plans to fix this annoyance? If so, in what version?
>
> Currently using:
> mysql Ver 14.12 Distrib 5.0.41, for pc-linux-gnu (i686)
>
>
>

Nope. I've only ever use mySQL. I only ever care to use mySQL. mySQL puts in
plenty of other features that no other RDBMS has or uses, and other RDBMS
have features that mySQL has, so what's the problem. Unless I was porting
to/from another RDBMS? It seems stupid that I can't do that though. I can
use the alias in the HAVING clause, and also in an ORDER BY clause. I'm not
saying it's a trivial change, I am saying that it would make a lot of sense
to do and I'm sure I'm not the first person to desire such a feature.

d

> -----Original Message-----
> From: Peter Brawley [mailto:peter.brawley@earthlink.net]
> Sent: Tuesday, October 23, 2007 5:39 PM
> To: Daevid Vincent
> Cc: mysql@lists.mysql.com
> Subject: Re: Why can't I still not use an ALIAS in the SELECT
> portion of an SQL statement?
>
> >When will I be able to do something seemingly
> >so basic as this re-use of an alias?
>
> Do you know an implementation of SQL which allows this?
>
> PB
>
> Daevid Vincent wrote:
> > When will I be able to do something seemingly so basic as
> this re-use of an
> > alias?
> >
> > SELECT DATE_ADD('2007-10-23', INTERVAL user_access_hours HOUR)
> > AS group_duration_date,
> > UNIX_TIMESTAMP(group_duration_date)
> > AS group_duration_date_timestamp
> > FROM end_user_groups;
> >
> > Error Code : 1054
> > Unknown column 'group_duration_date' in 'field list'
> > (0 ms taken)
> >
> > So instead I must do this very cumbersome and inefficient way:
> >
> > SELECT DATE_ADD('2007-10-23', INTERVAL user_access_hours HOUR)
> > AS group_duration_date,
> > UNIX_TIMESTAMP(DATE_ADD('2007-10-23',
> > INTERVAL
> user_access_hours HOUR))
> > AS group_duration_date_timestamp
> > FROM end_user_groups;
> >
> > *sigh*
> >
> > This has been a cause of frustration since mysql 3.x series.
> > Are there any plans to fix this annoyance? If so, in what version?
> >
> > Currently using:
> > mysql Ver 14.12 Distrib 5.0.41, for pc-linux-gnu (i686)
> >
> >
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=daevid@daevid.com
>
>

Daevid Vincent wrote:
> Nope. I've only ever use mySQL. I only ever care to use mySQL. mySQL puts in
> plenty of other features that no other RDBMS has or uses, and other RDBMS
> have features that mySQL has, so what's the problem. Unless I was porting
> to/from another RDBMS?

This strikes me as a *terrible* reason.

It seems stupid that I can't do that though. I can
> use the alias in the HAVING clause, and also in an ORDER BY clause.

Yes, but those are HAVING and ORDER BY clauses. You recognise that they
are distinct parts of a SELECT statement, so ...

I'm not saying it's a trivial change

So far, i'd say that you're suggesting it is.

http://dev.mysql.com/tech-resources/...sql-views.html

brian

mysql@subtropolix.org wrote:
> Daevid Vincent wrote:
>
>> Nope. I've only ever use mySQL. I only ever care to use mySQL. mySQL
>> puts in
>> plenty of other features that no other RDBMS has or uses, and other RDBMS
>> have features that mySQL has, so what's the problem. Unless I was porting
>> to/from another RDBMS?
>
>
> This strikes me as a *terrible* reason.
>
> It seems stupid that I can't do that though. I can
>
>> use the alias in the HAVING clause, and also in an ORDER BY clause.
>
>
> Yes, but those are HAVING and ORDER BY clauses. You recognise that they
> are distinct parts of a SELECT statement, so ...
>
> I'm not saying it's a trivial change
>
> So far, i'd say that you're suggesting it is.
>
> http://dev.mysql.com/tech-resources/...sql-views.html
>
> brian
>

I've looked for--but cannot find--a page online that explains this
nicely. Suffice to say that the problem is similar to that of trying to
use a column alias in the WHERE clause. In that case, it's because WHERE
is parsed before the SELECT clause. In your case, it's simply that the
column expressions are constructed more or less as one, not from left to
right or anything like that.

You could also use a derived table here, i should think:

SELECT foo.bar, UNIX_TIMESTAMP(foo.bar) FROM (
SELECT DATE_ADD('2007-10-23', INTERVAL user_access_hours HOUR)
AS bar
FROM end_user_groups
) AS foo;

brian