I have the following tables EMPLOYEE& DEPT.
I am writing the following query :
validtime as of date '2001-01-01'
from SANDBOX.TEMPORAL_EMPLOYEE AS EMP
join SANDBOX.TEMPORAL_DEPT as dept
Is it possible that I can create a view / macro so as to enable me to pass the validtime date dynamically?
|Table Name :||SANDBOX.TEMPORAL_DEPT|
|10||Dept 10||Dept 10 Desc||('1998-01-01', '2001-10-15')|
|10||Dept 101||Dept 101 Desc||('2001-10-16', '9999-12-31')|
|20||Dept 20||Dept 20 Desc||('2002-01-01', '2003-01-01')|
|20||Dept 200||Dept 200 Desc||('2003-01-02', '9999-12-31')|
|Table Name :||SANDBOX.TEMPORAL_EMPLOYEE|
You can create a parameterized macro which can take the date parameter from you.
If you are just running the query manually in SQL assistant and want to make the query dynamic so that everytime it ask you for the date, then you can do that as well by using '?<any_text>'
This is not difficult.
CREATE MACRO PassValidtime(mydate DATE) AS (
validtime as of :mydate
You can also pull the validtime date from a nested select as shown below.
validtime as of (SELECT CURRENT_DATE -1)