Snowflake View nested calculation

I have to build a view by fetching data from 7-8 tables and then there are field which are calculated from other calculated fields. For example first calculation is if(indicator=‘H’, amount*20, amount) as deliAmt. And then If(isnull(deliAmt),0 else deliAmt)

This is just an example but for this view i have 5-6 such calculations required.

Also the final view has around 7-8 main tables and other tables for these fetching columns for these calculations. In tolal there will be 57 columns finally.

Please guide what is the best approach to implement this.

Answer

To write a view that selects data from 7-8 tables, write the SQL the select from 7-8 table and put it “in a view”.

But the other part of you question of how to do IF like logic is to use in Snowflake the IFF operator thus your example if(indicator=‘H’, amount*20, amount) as deliAmt

would be written

IFF(indicator=‘H’, amount*20, amount) as deliAmt

and If(isnull(deliAmt),0 else deliAmt) would be:

IFF(isnull(deliAmt), 0, deliAmt)

of which can also be done via ZEROIFNULL like:

ZEROIFNULL(deliAmt)