All Forums Database
wrightjb 2 posts Joined 10/14
20 May 2016
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?

dnoeth 4628 posts Joined 11/04
20 May 2016

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
;

:-)

Dieter

wrightjb 2 posts Joined 10/14
23 May 2016

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

You must sign in to leave a comment.