When you give a calculated column the same alias as an existing column, how do you refer to the new value?

Database

When you give a calculated column the same alias as an existing column, how do you refer to the new value?

Normally when you define a column and give it an alias you can reference it in calculating another column:

SELECT
1 AS X,
-X AS NEG_X
;

Result:

X NEG_X
1| 1 | -1 |

But if there was already a column with that name in the table or sub-query you're selecting from, the alias continues to refer to the underlying column rather than the new definition:

SELECT
X+1 AS X,
-X AS NEG_X
FROM (SELECT 0 X) DUMMY
;

Result:

X NEG_X
1| 1 | 0 |

Normally I could just give the new definition a different alias to get around that, but I ran into this issue in the context of a recursive CTE, where the newly calculated value MUST have the same name as the underlying column (because you're recursively calculating it), e.g.:

WITH RECURSIVE THING (X,NEG_X) AS
(
SELECT
X,
-X AS NEG_X
FROM (SELECT 1 AS X) DUMMY
UNION ALL

SELECT
X+1 AS X,
-X AS NEG_X
FROM THING
WHERE X<5
)
SELECT *
FROM THING
;

Result:

X NEG_X
1| 1 | -1 |
2| 2 | -1 |
3| 3 | -2 |
4| 4 | -3 |
5| 5 | -4 |

In this situation I can't find any way to refer to the newly calculated value because the alias refers to the previous value of the field, which can be onerous if I need to use that value in multiple places.

So does anyone know how to explicitly refer to the most recent definition of an alias?

Tags (3)
2 REPLIES
Junior Contributor

Re: When you give a calculated column the same alias as an existing column, how do you refer to the new value?

the newly calculated value MUST have the same name as the underlying column

Why do you think this is necessary?

WITH RECURSIVE THING (X,NEG_X) AS
(
SELECT
X,
-X AS NEG_X
FROM (SELECT 1 AS X) DUMMY
UNION ALL

SELECT
X+1 AS bla,
-bla AS NEG_X
FROM THING
WHERE X<5
)
SELECT *
FROM THING
;

:-)

Re: When you give a calculated column the same alias as an existing column, how do you refer to the new value?

Ah, I guess I forgot that union doesn't rely on the column aliases. Thanks Dieter, that solution works for me!