How to use decision logic in Macro?

Database
Enthusiast

How to use decision logic in Macro?

Hi! I'm converting a SQL Server stored procedure to Teradata Macro because it returns a result from one of 8 queries. The query to be executed depends on the input parameter.
But it seemed Teradata V2R6 Macro does not allow either IF-ELSE nor CASE statement to execute a query in the THEN expression.
So how do people do decision switching inside of a Macro?

This is what I intend to do( simplified to illustrate my point):
CREATE MACRO test( option int, min INT) AS
{
CASE (i)
WHEN 1 THEN SELECT A.B FROM TABLE1 WHERE C > :min;
WHEN 2 THEN SELECT X,Y FROM TABLE2 WHERE Z > :min;
ELSE SELECT A,B,C,D FROM TABLE 1;
END CASE;
}

Thank you.
Regards, Nancy
4 REPLIES
Teradata Employee

Re: How to use decision logic in Macro?

Teradata macros are multistatement requests. There are no control statements for macros (unless you count the ABORT test to roll back the entire unit of work).

You can do what you outline with Teradata 12.0 Stored Procedures. In earlier releases you'll likely have to change the requesting application.
rgs
Enthusiast

Re: How to use decision logic in Macro?

Here is the equivalent Stored Procedure to do what you want in Teradata 12.0:

CREATE PROCEDURE test(IN optionv INT, IN minv INT)
DYNAMIC RESULT SETS 1

BEGIN

DECLARE select_1 CURSOR WITH RETURN ONLY TO CLIENT FOR
SELECT A,B FROM TABLE1 WHERE C > minv;

DECLARE select_2 CURSOR WITH RETURN ONLY TO CLIENT FOR
SELECT X,Y FROM TABLE2 WHERE X > minv;

DECLARE select_3 CURSOR WITH RETURN ONLY TO CLIENT FOR
SELECT A,B,C,D FROM TABLE1;

CASE optionv
WHEN 1 THEN OPEN select_1;
WHEN 2 THEN OPEN select_2;
ELSE OPEN select_3;
END CASE;

END;
Enthusiast

Re: How to use decision logic in Macro?

Thank you, that's exactly what I wanted to do; but unfortunately we have to support Teradata V2R6.
Enthusiast

Re: How to use decision logic in Macro?

Hi,

I would like to explore the advanced features in teradata 12.0 especially in stored procedures.

Could you please let me know where you got the material for Teradata 12.0 stored procedure?

Regards,
Dinesh K