Thread: referencing column aliases in select list

Hi,

I have a SELECT statement that is a bit involved in terms of
calculations, so I wanted to set up some column aliases and refer to
them further down the select list:

---<---------------cut here---------------start-------------->---
SELECT table1.col1 - table2.col1 AS diff1,
    table1.col2 + table2.col2 AS sum1,
    sum1 - diff1
FROM table1 INNER JOIN table2 ON (table1.id = table2.id)
---<---------------cut here---------------end---------------->---

but this fails with the error message that sum1 column is not known.
How can one get around this?  The calculations are quite involved and
would be difficult and error-prone to repeat them anywhere they're
needed in the select list.  Thanks in advance for any pointers.


Cheers,

--
Seb

Seb wrote:
> ---<---------------cut here---------------start-------------->---
> SELECT table1.col1 - table2.col1 AS diff1,
>     table1.col2 + table2.col2 AS sum1,
>     sum1 - diff1
> FROM table1 INNER JOIN table2 ON (table1.id = table2.id)
> ---<---------------cut here---------------end---------------->---
>
> but this fails with the error message that sum1 column is not known.
> How can one get around this?  The calculations are quite involved and
> would be difficult and error-prone to repeat them anywhere they're
> needed in the select list.  Thanks in advance for any pointers.

The way I usually handle this is as follows.

SELECT foo - bar AS baz FROM (
   SELECT a.a + b.a AS foo, a.b + b.b AS bar FROM a JOIN b ON a.id = b.id
) AS subtable;

Although, I'm not really sure that's The Right Way to do it.

Colin

On Fri, 04 Apr 2008 00:35:01 -0400,
Colin Wetherbee <cww@denterprises.org> wrote:

[...]

> SELECT foo - bar AS baz FROM ( SELECT a.a + b.a AS foo, a.b + b.b AS
> bar FROM a JOIN b ON a.id = b.id ) AS subtable;

> Although, I'm not really sure that's The Right Way to do it.

Thanks Colin, yes, I thought about that, but things get nasty when you
have to do some other calculation with 'baz', with the output of that
and so on (as I'm facing in my real tables).  But this may really be the
best solution anyway.


Cheers,

--
Seb