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 '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.
SEL COL1,COL2 FROM TABL1 ;
Also can dynamic SQL be run inside a macro.
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.)
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.
CASE WHEN POSITION ('%' IN NAME) >0 THEN 'PRESENT' ELSE 'NOT'
CASE WHEN POSITION ('%' IN ADDRESS)>0 THEN 'PRESENT' ELSE 'NOT'
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.
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).