Macros

Data Modeling
Enthusiast

Macros

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)
1 REPLY
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?