Sunday, June 14, 2015

Teradata:Can you use both DDL and DML in a macro?

As a database developer you work with Store Procedure and Macros. It's depends on your problem which one to choose from macro and store procedure. However, this post is not about comparing two of them. Rather, we will describe a scenario for macro that cause error and you may stumble solving error until you know macro has it's limitation.

Example Scenario: You need to develop macro that can create a table and insert data into the table and at last return rows from the table.

So your SQL query will look like below:

Create table Customer(
cust_id int,
Cust_First_Name varchar(40),
Cust_Last_Name varchar(40));
DDL
insert into Customer
values(1,'dipu','Paul');

DML
Select * from Customer

DML

If you run the above SQL (without creating macro), then it will create the table named 'Customer', insert data into the table and finally retrieve row from the table.

Lets wrap this code in a macro, which will look like below:

Create MACRO Customer_Operation AS(
create table Customer(
cust_id int,
Cust_First_Name varchar(40),
Cust_Last_Name varchar(40));

insert into Customer
values(1,'dipu','Paul');

select * from Customer;
);

You wrapped the same code which was running successfully earlier but now it will return error code like below:


                                Fig: Error  3576:  Data definition not valid unless solitary.

The error raise due to the fact that macro has built with mixing of DDL and DML that doesn't support Teradata macro.

So you need to keep in a mind while working with macro "You cannot mix DDL and DML requests within the same macro."