Correctly referencing a newly computed column with the same name-CodePudding

I am using Snowflake, and I have a couple of old tables that I am using to generate a new table. The new table has to have the same column names as the old tables. To make the new table, I do a join. Simplified a little, the SQL looks like this -

create table NEW_TABLE as
  select
    f(A.abc, B.abc) as abc
  from OLD_TABLE A
  join OTHER_TABLE B on <conditions>
  where f(A.abc, B.abc) > 0

It works. But the where part is not good because I have to recompute f(A.abc, B.abc). If I alias abc as something else like select f(A.abc, B.abc) as abc2 I can simply reference abc2 in the where. However, then NEW_TABLE will have a column named abc2 which is wrong. If I don't alias abc, then anytime I reference abc, it uses table A's value for abc instead of the newly computed one. What is the right solution here?

CodePudding user response：

When using:

select
  f(A.abc, B.abc) as abc
from OLD_TABLE A
join OTHER_TABLE B on <conditions>
where abc > 0;

Condition abc > 0 cannot use the alias defined at the select level as it is overshadowed by column name.

Using different alias than already existing column is a solution,but does not provide value as it is not anticipated column name.

select
  f(A.abc, B.abc) as alias_not_existing_in_table
from OLD_TABLE A
join OTHER_TABLE B on <conditions>
where alias_not_existing_in_table > 0;

SAS's SQL dialect support calculated keyword designed exactly for such scenario. Pseudocode:

select
  f(A.abc, B.abc) as abc
from OLD_TABLE A
join OTHER_TABLE B on <conditions>
where calculated abc > 0;

An alternative approach is using LATERAL JOIN and explicitly prefix the expression:

SELECT
   s.abc
FROM OLD_TABLE AS A
JOIN OTHER_TABLE AS B
  ON <conditions>
,LATERAL (SELECT f(A.abc, B.abc) AS abc) AS s
WHERE s.abc > 0;

This way there is explicit alias that allows to distinguish between A.abc and a new computed value.