How to declear Macro Variable?

Database

How to declear Macro Variable?

Anyone likes to help?

Say I have the code below. How to create macro variables
for eom_date, product, member_age, enroll_status

select *
from product_table
where eom_date>='2007-01-01'
and product='PPP'
and member_age>50
and enroll_status='E';

Thanks a lot,

9 REPLIES
N/A

Re: How to declear Macro Variable?

Varaibles are defined after the macro name and accessed using a colon in front of it

REPLACE MACRO georgexu (
eom_date DATE
,product CHAR(3)
,member_age INT
,enroll_status CHAR) AS
(
SELECT *
FROM product_table
WHERE eom_date>= :eom_date
AND product= :product
AND member_age> :member_age
AND enroll_status= :enroll_status;
);

Dieter

Re: How to declear Macro Variable?

dnoeth:

Thanks a lot.

Does it work in "Teradata SQL Assistant", or it need a shell file to call
it?

If shell file, how to do it? Thanks,

Sorry to bug this. I am a newbie.
N/A

Re: How to declear Macro Variable?

Of course this works in SQL Assistant or any other query tool, only Stored Procedures need a file to run the COMPILE command in BTEQ.

Dieter

Re: How to declear Macro Variable?

Dieter:

One final quest: Why the SQL below give a error message.
I tried it inside "Teradata SQL Assistant"

REPLACE MACRO temp1 (stdt DATE,prod CHAR(3) ) AS
(select cx.CIMS_ACCT_KEY ,
PRDCT_TYP_CDE,
ORGNL_SRC_CDE,
TRND_MTH_END_DT
from v_cdss.VC_FEE_PRDCT_ENRLL_TM_SER as cx
where TRND_MTH_END_DT>=:stdt
and PRDCT_TYP_CDE=:prod);

exec temp1('2007-12-31','DAG');

________________________________
Code = 3706.
3706: Syntax error: expected something between the word 'prod' and ')'.
Output directed to Answer set window
N/A

Re: How to declear Macro Variable?

You simply missed the final semicolon before the closing bracket:

and PRDCT_TYP_CDE=:prod;);

Dieter

Re: How to declear Macro Variable?

It still says

Code = 3932.
3932: Only an ET or null statement is legal after a DDL Statement.
Output directed to Answer set window

N/A

Re: How to declear Macro Variable?

This seems to be a bug in some versions of SQL Assistant, instead of submitting both the CREATE and the EXEC as one block using F5, highlight the CREATE, submit it and then do the same with the EXEC (or simply open another tab for the EXEC).

Dieter

Re: How to declear Macro Variable?

I want to create a global variable date and simply pass that date in another tables. What code should be used to create such a variable? I havent got any examples where we can create such a variable

N/A

Re: How to declear Macro Variable?