Multiple selects in macro

Database

Multiple selects in macro

We have a macro with multiple select statements.  When each statement has records to return they all run.  If the first select statement returns no records all subsequent select statements return no records.

Example:

replace macro db.mn

as (

Select *

from db.table

where row_num = 10

;

Select *

from db.table

where row_num = 11

;

)

In the case when row_num has values of both 10 and 11 this macro generates to results sets.  When row_num has value of 10 but not 11 the macro generates a valid result set for the first statement and an empty (valid) result set for the second statement.

The problem:  When row_num has no value 10 and has value 11 the macro generates an empty result set for the first statement (valid) and an empty result set for the second statement (not valid!).

How should this be written so that the macro will run if the first statement finds no records?

Thanks,

Brian

3 REPLIES
Junior Contributor

Re: Multiple selects in macro

Hi Brian,

you run that macro through SQL Assistant?

This issue has been fixed, try to install the latest release.

Dieter

Re: Multiple selects in macro

Indeed, through SQLA.  I'm using 13.10 is there a newer version?

Junior Contributor

Re: Multiple selects in macro

There's already 13.11:

http://developer.teradata.com/tools/articles/what-s-new-in-teradata-sql-assistant-13-11

I just tried it on:

- 13.11.00.02: ok

- 13.10.00.06: ok for a .NET connection, but for ODBC it's still wrong

The latest patch level for 13.10 is probably 13.10.0.8, maybe this corrects it.

You might also post a coment in Mike Dempsey's article on 13.10 when it will be fixed.

Dieter