How can I use a calculated field in another calculation further down in the query?-CodePudding

If I have a table that has a few native fields, such as: monthly written premium. But I used that field in combination with some date logic to calculate and create fields in the query called: CY YTD Written premium; PY YTD Written premium; and PY Written Premium.

How can I use those created fields in another calculation further down in the query.

What I need to do is take YTD Written Premium - PY YTD Written Premium CY YTD Written Premium to create LTM Written Premium.

Snowflake will not allow me to reference the 3 fields needed to make LTM because they are not native to the table.

I know it has to be some sort of embedded query, but I'm unsure on the syntax.

CodePudding user response：

Snowflake supports inline column lateral reference, thus referencing alias at the same SELECT level will work as long as there is no nesting of windowed functions or shadowing table columns:

SELECT 
   expr AS col1,
   expr2 AS col2,
   col1   col2 AS col3
FROM table;

Sample (col1, col2, col3 are expressions):