create macro issues

Database
N/A

create macro issues

Good Day!
I am a user with limited permissions to our Teradata data warehouse. I have "Create Macro" permissions, but not "Create Procedure" permissions.

I have 30 queries I need to run sequentially to complete a process. These queries are stored in text (.sql) files and I have to run them individually and commit the results. I would like to be able to keep the queries in the text files and create a macro to go out, open, run and commit one at a time. I would like to keep the queries in the text files in order to make it easier to edit the queries.

This is an example of what I'm trying to do:

Create Macro mcr_example as
(
run C:\query1.sql
run C:\query2.sql
run C:\query3.sql
.
.
.
);

The error I get is 3706: Syntax error: expected something between '(' and the word 'run'

Please tell me how I'm supposed to do this? I have looked at all the documentation our company has and the Internet and there is limited documentation on Teradata macros.

Thank you

4 REPLIES

Re: create macro issues

I beleive RUN is bteq command and is used to run scripts from a logged on session for eg:-

.logon user
password user

.run file= script.sql;

.logoff

I hope you cannot use run as you have mentioned in your example.

If you still want to use Run to run your scripts there are several ways to do it.One way that I can think of is to run the scripts back-back in a single session as shown below

.run file = sql1;
.run file = sql2;
......

other method is to use nested calls to each script file .That is to say

file sql1

select .......;
.run file = sql2;

file sql2

select......;
.run file =sql3;

file sql3
blah blah blah blah....

and call sql1 from the bteq session that you have logged on.

.logon user
password user

.run file =sql1;

.logoff

There could be several other ways to acheive your purpose.Please refer to BTEQ manual for further information
N/A

Re: create macro issues

One more comment on MACRO.
Macros are always an explicit transaction. Either ALL commit or rolled back.

Vinay
N/A

Re: create macro issues

Thank you for your prompt reply. However, I failed to mention that I'm working in SQL Assistant, not BTEQ. Would it be possible for the users to run the macro in SQL Assistant if I create it in BTEQ?

Thank you

Re: create macro issues

Hi Taju,

Macro's created in SQL Assistant can work in BTEQ. It is independent of client(BTEQ,SQL Assistant etc.,).
and you cannot use RUN command in sql Assistant. Since this is a BTEQ command.