Why do we need a macro? If there is a set of frequently used SQL statement which are being repeatedly executed, this set of SQL statement can be packed inside block and can be defined as a MACRO. A macro can include also execute another macro.
To create a macro to select today’s date:
CREATE MACRO today_date as (SELECT date;);
To execute a macro: EXEC today_date;To drop a macro:DROP macro today_date;A macro can also be used to insert row into tables with more than one input. Here is an example of a macro with multiple inputs which inserts a row to an emp table. Make sure that the table already exists.CREATE MACRO insert_into_emp
(
emp_no INTEGER,
dept_no VARCHAR(12),
first_name CHAR (20),
last_name”CHAR (20),
salary” INTEGER
)
AS
(
INSERT INTO emp
(emp_no, dept_no, first_name, last_name, salary)
values (:emp_no, :dept_no, :first_name, :last_name, :salary)
);
Popularity: 65% [?]
Our Random Articles
- New Year Wishes 2010
- New Year Wishes
- How to use EXTRACT function with date and time columns
- How to Post on a Separate/New Page in WordPress
- Creating Column Aliases in Teradata
More Links
11 Comments
how to create a macro which will execute the sql statements in it sequentially.
Eg:
sql1
success
sql2
else
exit
Basically you are asking for multiple SQL statement processing, conditionally. You have to use BTEQ commands here. Here is an example which runs fine in a BTEQ session.
CREATE MACRO test AS
(SELECT date;
ECHO ‘.IF ERRORCODE = 0 THEN’;
SELECT time;);
Hope this helps.
Hi,
Can we create and execute macros inside the procedure and how to use this?
Thanks & Regards
Punam
Hi Punam,
As per the Teradata rules, the EXECUTE statement is not supported by SPs. Only way we can use a Macro in SPs, is to use it as part of DECLARE CURSOR(macro form) within a Stored Procedure.
More to add, SP’s are designed to operate sequentially and not in parallel and when Macros return large set of rows as a recordset, SPs would have to process a lot more than the usual.
Hope this helps.
so, is there a way to use results of a select that is stored inside a teradata macro ?
f.e. if we have a macor defined as:
replace macro abc as (
select * from abc.abc;
);
is there a way to do something like this:
select * from ()
?
it should be
select * from (results of executing macro abc)
How to create marco which will output the data to a pipe delimited file?
I have a macro that needs to accept a comma separated list of values-to be used in a sql - IN() clause.
create macro test(user varchar(1000)as
(
select * from user_list where user_id in (:user)
);
The problem is that whatever i enter into the parameter, the macro assumes it to be a single value instead of multiple values.
So if pass a single user, the macro returns values, but if i pass more than one eg: “100,101″ no records returned
Would appreciate any kind of help.
Hi Raman,
Thank you for your question.
As per my understanding A “simple” Macro can not do this. There has to be a Stored Procedure. Try creating Stored Procedure and give it a shot.
Following code might help in creating a Stored Proc.
CREATE PROCEDURE MyProc(IN Values VARCHAR(150))
READS SQL DATA
DYNAMIC RESULT SETS 1
BEGIN
DECLARE InputStatement VARCHAR(500);
DECLARE ResultSet CURSOR WITH RETURN ONLY TO CLIENT FOR FirstStatement;
SET InputStatement = ‘SELECT * FROM TableName WHERE ColumnName IN (’ || Values || ‘);’;
PREPARE FirstStatement FROM InputStatement;
OPEN ResultSet;
END;
Hope that helps.
Hi,
I am new to teradata. I have some questions regarding Macros in Teradata.
1) what’s the difference between a Macro and Stored Procs?
2) Can we execute DDL in a Macro?
Thanks in advance,
Nitin
Hi Admin, thanks for the q&a. I got query with Macro here. FYI,Im using Teradata12 and I ve tried Macro with numerous DML’s and as per Teradata rule, Macro can have only one DDL, that too as last stmt. Since DDL locks DD, we are using it in last line. but my question is I ve tried Macro with combination of DML n DDL, and im getting warning as “Data Dictionary must be solitary” and couldnt exec macro, Pls help me.\
Thanks,
Rajesh
Popular Articles