How to join to an alias column in SQL Server?

alias, inner-join, sql, sql-server

Solution

The SELECT-column alias is practically the last thing to be applied to the statement and as such is not available for joins "lower down" that form part of the query as a whole. You can however access the alias if referenced from an outer select

e.g.

select my_code from
(
select 1 as my_code from ....
) x

Problem

I have the followig problem. When the input is empty I want to replace it with the 'yellow_code' column aliased as 'Code'. When I attempt an inner join on the aliased column 'Code' to 'GrantCode' on another table, I get the following error description: "Invalid column name 'Code'". Anyways to bypass this?

Original source