Combining Results from multiple queries into one

General
General covers Articles, Reference documentation, FAQs, Downloads and Blogs that do not belong to a specific subject area. General-purpose Articles about everything and anything
Enthusiast

Combining Results from multiple queries into one

 

Hi,

 

How to concatenate the results of 2 queries into 1 because when when i am executing below 2 queries then it is coming in 2 separate windows.

I want to combine the results into 1.

 

SEL 'SEL ';

SEL 'COL1,COL2';

SEL 'FROM TABL1 ;';

 

When i run these queries together result is coming in 3 separate windows is there a way to combine the results into 1 so that complete query is created as below which i can later use.

 

Query :

SEL COL1,COL2 FROM TABL1 ;

 

Also can dynamic SQL be run inside a macro.

 

Thanks.

 

 

3 REPLIES
Teradata Employee

Re: Combining Results from multiple queries into one

What's the real problem you are trying to solve?

 

To combine query results, you either have to make them subqueries and JOIN them somehow.

Or use a set operator (e.g. UNION ALL) if the results are compatible, but then they would be rows instead of one concatenated string.

 

No, macros cannot use dynamic SQL. (Macros can have parameters where a value is expected, but not a name or SQL keyword.)

Enthusiast

Re: Combining Results from multiple queries into one

Hello Fred,

 

Basically i want to identify which all columns in a table has a particular character let's say using below query  but this query is for a single table and i want to generate a dynamic query which can be used for any table and it will have input as the table name and will extract the columns from DBC.COLUMNSV.

 

SEL DISTINCT

CASE WHEN POSITION ('%' IN NAME) >0 THEN 'PRESENT' ELSE 'NOT'

CASE WHEN POSITION ('%' IN ADDRESS)>0 THEN 'PRESENT' ELSE 'NOT'

.

FROM TABLE1

 

 

I was trying to do this using macro but since macros can not run dynamic SQL so i am now trying now with stored procedure.

 

Please let me know if there is a better approach.

 

Thanks.

 

Teradata Employee

Re: Combining Results from multiple queries into one

Using a SP that returns a "DYNAMIC RESULT SET" seems like the right solution for something like thils.

Create the SELECT statement as a character string, PREPARE the string as a statement, OPEN a cursor for the statement (and leave it open to return to the caller).