CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
sallen
sallen

Reputation: 25

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

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.

Upvotes: 1

Views: 863

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175934

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):

enter image description here

Upvotes: 3

Related Questions