All Forums Database
nshen_Infor 11 posts Joined 12/07
28 Dec 2007
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

Regards, Nancy Infor Global Solutions, Mountain View, CA
Fred 1096 posts Joined 08/04
31 Dec 2007

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 106 posts Joined 02/07
07 Jan 2008

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 1BEGIN 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;

nshen_Infor 11 posts Joined 12/07
09 Jan 2008

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

Regards, Nancy
Infor Global Solutions, Mountain View, CA

lordbwithme2 7 posts Joined 12/08
28 Dec 2009

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

You must sign in to leave a comment.