retrieve row count from Teradata Macro

Database

retrieve row count from Teradata Macro

Hi,

I have a Macro 'ABC' which would take one input parameter. I am executing the macro as execute ABC(?) through jdbc.

This Macro returns results with column values. However I need to  get the number of rows this Macro returns.

Is there something like select count(*) from Macro we can use?

1 REPLY
Teradata Employee

Re: retrieve row count from Teradata Macro

You must use the EXECUTE MacroName command to execute a macro, and No, it is not possible to use an execute command as a sub-select or derived table.

Hypothetical SQL syntax that is NOT supported:

select count(*) from (execute MacroName) as dt

A macro can contain (and is generally expected to contain) a multi-statement request. The syntax for a sub-select returning a derived table isn't compatible with a multi-statement request.

But since a macro can contain a multi-statement request, have you considered adding the select count(*) to your macro?

For example, let's assume that your macro is defined like this:

create macro foo(p1 integer) as (select * from tab1 where c1 = :p1;)

You could change your macro to contain two queries -- the first query would return the row count, and the second query would return the rows, like this:

create macro foo(p1 integer) as (
select count(*) from tab1 where c1 = :p1;
select * from tab1 where c1 = :p1;
)

The last thing to keep in mind when using the Teradata JDBC Driver with multi-statement requests is how to execute the multi-statement request and how to obtain the multiple results. Please refer to the following section of the Teradata JDBC Driver User Guide:

http://developer.teradata.com/doc/connectivity/jdbc/reference/current/jdbcug_chapter_2.html#CHDEHDFA

Multi-statement requests can be executed using Statement.execute() method. Each result may be either a result set or an update count. The application can retrieve a result set from the Statement.getResultSet method, or retrieve an update count from the Statement.getUpdateCount method. When multiple results are returned, then the application must use the Statement.getMoreResults method to iterate through these results.

For the revised example "foo" macro, the macro returns two result sets. The application must do the following:

PreparedStatement ps = con.prepareStatement("execute foo(?)");
ps.setInt(1, 123); // specify value for parameter marker
ps.execute(); // ignore return value because we expect RS
ResultSet rs = ps.getResultSet();
rs.next(); // advance to first (and only) row
int nRowCount = rs.getInt(1);
ps.getMoreResults(); // advance to second RS
rs = ps.getResultSet();
while(rs.next())
{
 // process each row of RS
}

We offer many sample programs that illustrate how to use the Teradata JDBC Driver with macros and multi-statement requests:

http://developer.teradata.com/doc/connectivity/jdbc/reference/current/samplePrograms.html

Please refer to section 7 for sample programs that work with multi-statement requests. Please refer to section 8 for sample programs that work with macros.