asked on

Dynamic Query Need

Ok, so I have a clue but not enough focus to find the final answer here. So . . . .

I have a query that goes something like:

select ResultName
from MyDB
where name like 'xyz%'

In the above, there could be 1 result or there could be 20 based on when I run the query.

I need a loop that will allow me to run a query such as:

select * into #tempResults
from (
select 'ResultName', OtherItem
from ResultName
)

I need this to run and insert into a temp table one time for each result of the first query.

My apologies in advance for the messy way I presented this. I'm pretty sure I can adapt once I get an example. I'm using DB2 for i series, but I'm sure I could adapt it from MS SQL or anything similar.

Thanks for your assistance!

Mike Eghtebas

select ResultName
into #tempResults
from MyDB
 where name like 'xyz%'

Open in new window

Kent Olsen

Hi Ray,

What do you want to do with the results? For most uses you should be able to structure a query so that the number of results is irrelevant.

For the unusual case where you do need to manage the data one row at a time a cursor will do fine.

Kent

Ray

ASKER

KDO - The results of the first query are only to get session/table names to be used in the second query. So I will need to run a query that takes results from q1.result1 query into a temp, then q1.result2 query into the same temp and so on until each session/table from the original query has been run in the secondary query (resultX).

eghtebas - that is simply inserting into a temp table, that part I have figured out. I need help with the LOOP part so I can run the query INTO the temp table one time for each result from another query.

Kent Olsen

Perhaps something like this:

DECLARE c1 CURSOR FOR SELECT name FROM mytable WHERE {condition};
DECLARE vname VARCHAR(50);
DECLARE statement VARCHAR(500:

OPEN CURSOR c1;
WHILE (SQLCODE == 0)
  FETCH C1 INTO vname;
  SET statement = 'INSERT INTO TEMP SELECT * FROM ' || vname;
  EXECUTE IMMEDIATE statement;
END WHILE;

Open in new window

SOLUTION

Member_2_2484401

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ray

ASKER

Dave, I don't have the privileges to add an SP :-(

Kdo, I'll try in the morning.

Any other posts I'll look at them as well.

Thanks you everyone for your efforts so far !!

SOLUTION

Kent Olsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Ray

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ray

ASKER

splitting the points between the two most helpful and that pushed me towards my final resolution.