Temporal Macro / Dynamic SQL in a View

Database

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')
5 REPLIES
WAQ
Enthusiast

Re: Temporal Macro / Dynamic SQL in a View

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

Re: Temporal Macro / Dynamic SQL in a View

Hi,

Do you have a sample macro like this for temporal?

Thanks.

Shraddha.

WAQ
Enthusiast

Re: Temporal Macro / Dynamic SQL in a View

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

Re: Temporal Macro / Dynamic SQL in a View

Nope. I want to parameterize the following clause:

validtime as of date '2001-01-01'

Teradata Employee

Re: Temporal Macro / Dynamic SQL in a View

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;