All Forums Database
sdmody1983 3 posts Joined 08/12
06 Nov 2012
Temporal Macro / Dynamic SQL in a View

Hi,
I have the following tables EMPLOYEE& DEPT.
I am writing the following query :
 
validtime as of date '2001-01-01'
select *
from SANDBOX.TEMPORAL_EMPLOYEE AS EMP
join SANDBOX.TEMPORAL_DEPT as dept
on emp.dept_id=dept.dept_id;
 
 
Is it possible that I can create a view / macro so as to enable me to pass the validtime date dynamically?
 
Thanks.
Shraddha.
 
 
 

Table Name :

 SANDBOX.TEMPORAL_DEPT

 

 

DEPT_ID

DEPT_NAME

DEPT_DESC

AS_OF_DT

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

 

 

EMP_NO

EMP_NAME

DEPT_ID

AS_OF_DT

100

ABC

10

('2001-01-01', '2002-12-15')

100

PQR

20

('2002-12-16', '9999-12-31')

200

XYZ

20

('2001-01-01', '2002-12-15')

200

LMN

10

('2003-12-16', '9999-12-31')

300

JASON

10

('2001-01-01', '9999-12-31')

 

WAQ 158 posts Joined 02/10
06 Nov 2012

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>'

sdmody1983 3 posts Joined 08/12
07 Nov 2012

Hi,
Do you have a sample macro like this for temporal?
Thanks.
Shraddha.

WAQ 158 posts Joined 02/10
07 Nov 2012

Do you want to parameterized the WHERE clause in the query?

sdmody1983 3 posts Joined 08/12
09 Nov 2012

Nope. I want to parameterize the following clause:
validtime as of date '2001-01-01'
 
 

fnewbrough 17 posts Joined 05/11
08 Jan 2013

This is not difficult.
 
CREATE MACRO PassValidtime(mydate DATE) AS (
validtime as of :mydate
select *
from
MyTemporal_Table;)
EXEC PassValidtime('2012-12-01')
You can also pull the validtime date from a nested select as shown below.
validtime as of (SELECT CURRENT_DATE -1)
select *
from
MyTemporal_Table;

You must sign in to leave a comment.