Data Modeling


I have a requirement to write a complex view on Datawarehouse tables. 


something like this


create view as 

sel * from tablea  a

where exists

(sel 1 from tableb b where a.col1=b.col1 and b.col3 in ( select max(id1) from tabled ))

and not exists

(sel 1 from tablec c where a.col2=c.col2 and c.col3 in ( select max(id1) from tabled ))

and a.col3 in ( select max(id1) from tabled)


This is just a part of my view and there are many other tables and joins but with a filter of ( select max(id1) from tabled ) used multiple times for all the tables. 


Instead of using ( select max(id1) from tabled ), can i create a macro which returns ( select max(id1) from tabled ) and then use that macro in my view instead of ( select max(id1) from tabled ). ? If yes how to do it?


If yes, does this mean that macro will be executed only once irrespective of number of times used in view?



Tags (3)
Senior Supporter

Re: Macros

you can't use macros in views.


Querstion: Is the view exposed to users or to etl processes?

Is tabled a small table to a big table?