CODEWITHSUNDEEP
sqlmacrosteradata
Grigory P
Grigory P

Reputation: 191

Does Teradata support macro variables or smth or anything like that?

For example, I have an report that should be refreshed monthly, and the code looks like this:

SELECT *
FROM table1
WHERE report_date = '2017-10-01'
;

SELECT a.*, b.*
FROM table1 a
JOIN table2 b on a.key = b.key
WHERE b.date = '2017-10-01'
;

Is there any possibility to replace date in every statement with macro variable, that is invoked once? The code might look like this:

LET macro_var = '2017-10-01';

SELECT *
FROM table1
WHERE report_date = <macro_var>
;

SELECT a.*, b.*
FROM table1 a
JOIN table2 b on a.key = b.key
WHERE b.date =  <macro_var>
;

Thanks

Upvotes: 0

Views: 3592

Answers (2)

jhon fredy rodriguez acosta
jhon fredy rodriguez acosta

Reputation: 1

i am using your code, but i show the next error

Creat4 Macro Failed 3524: The user does not have CREATE MACRO access to database PROD_DWH_CONSULTA

CREATE MACRO macro1(r_date date) AS (
  SELECT *
  FROM PROD_DWH_CONSULTA.ENTIDADES
  WHERE Fecha_Act = :r_date;
);

exec macro1(DATE '2017-10-01'); -- recommended

Upvotes: 0

zarruq
zarruq

Reputation: 2465

In teradata, you can create a parameterized macro for this as below.

CREATE MACRO macro1(r_date date) AS (
SELECT *
FROM table1
WHERE report_date = :r_date;

SELECT a.*,
       b.*
FROM table1 a
JOIN table2 b ON a.key1 = b.key1
WHERE b.report_date = :r_date;
);

You can run it using

exec macro1('2017-10-01');

OR

exec macro1(DATE '2017-10-01'); -- recommended

Update:

In Teradata SQL Assitant, to insert variables into your code is to use a question mark followed by the variable name as below.

SELECT *
FROM table1
WHERE report_date = '?r_date';


SELECT a.*,
       b.*
FROM table1 a
JOIN table2 b ON a.key1 = b.key1
WHERE b.report_date = '?r_date';

Upon pressing F5 or Execute button, it will pop up for value for variable r_date

Upvotes: 1

Related Questions