Looping a Macro ??

[jones1]

Hi,

I have a macro that is passed in a key field, and returns multiple rows. I need to be able to run this macro in a loop with input provided dynamically (from a table).

I thought about using a stored procedure, but according to documentation a stored procedure can only return one row. I thought about using a macro, but I cann't figure out how to loop in a macro (based on documentation it agains appears to be something a stored procedure only can do). Therefore, I'm stuck.

I will then need to output the results of this query into a table and use it to query against another table, but if I can get the macro to loop, I think I can handle the rest.

 

[rrrkrishnan]

You can use cursors in stored procedures which can operate on a result set of one or more rows.

 

[jones1]

Thanks for your response.

However, even though the procedure can operate on more than one row, according to documentation it CAN NOT return more than one row which is what I need.

 

[dnoeth]

"I have a macro that is passed in a key field, and returns multiple rows. I need to be able to run this macro in a loop with input provided dynamically (from a table).
I will then need to output the results of this query into a table and use it to query against another table."

This sounds to me like all you need is a _single_ query. This will probably run magnitudes faster than your procedural approach.
If you could provide us more information incl. table & macro DDL we could probably help you to rewrite it.

"However, even though the procedure can operate on more than one row, according to documentation it CAN NOT return more than one row which is what I need."

You can't select, but you can insert the result of a query into a table.

Dieter

 

[jones1]

I realize that doing this iterative looping is probably very bad - the performance will be horrible. However, the business requirement is to check the results of the macro code against the results from another query. Therefore, I have run the macro as described in order to meet the purpose of doing this.

So, it sounds like using a cursor in a procedure, outputing the cursor to a table will work, all within the one procedure will work. Can I execute a macro within a stored procedure?

I can't even seem to be able to create a procedure at all. Here's the sample: create procedure test ()
begin
end;

Syntax error: Invalid SQL statement

I'd like to send the SQL as requested; however, it would amount to 13 pages just for the macro (not including the table DDL).

 

[dnoeth]

"However, the business requirement is to check the results of the macro code against the results from another query"

I still think you can do it in a single query ;-)

"create procedure test ()
begin
end;

Syntax error: Invalid SQL statement"

Which version is your Teradata, SPs are V2R4

"I'd like to send the SQL as requested; however, it would amount to 13 pages just for the macro"

You could email it to me: dnoeth@gmx.de

Dieter