Re: Dynamic SQL using INSERT with a predefined query and an id.

From: Brian Tkatch <SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK>
Date: Wed, 27 Jun 2001 14:53:05 GMT
Message-ID: <3b39ed80.407707000@news.alt.net>

On Tue, 26 Jun 2001 20:49:45 -0700, "Daniel A. Morgan" <dmorgan_at_exesolutions.com> wrote:

>Brian Tkatch wrote:
>
>> We're thinking about using a table to store queries. These queries
>> will then be run and will populate another table. However, each
>> population needs a unique id, but has to be the same for every row in
>> the population.
>>
>> So, the query can be gotten in a procedure via "SELECT Query INTO
>> The_Query FROM Query_Table WHERE Id = The_Id".
>>
>> If no id were needed, the following statement could be issued.
>> "EXECUTE IMMEDIATE 'INSERT INTO The_Table(Object_Id) ' || The_Query;.
>> Alas, I need to add a unique id. The query itself cannot pop a value
>> off a sequence because every one of its rows needs the same value.
>>
>> Any ideas?
>>
>> Brian
>
>I am a bit confused about what you id constraints are with respect to the
>table. But what is the purpose of creating this unusual arrangement. Is
>it possible that another solution would meet your requirement?

Yes, it is possible that there is another solution. But, this as well might be a very good approach.

I'll try to explain. Imagine a set of lists that must be created every day. Each list is generated from a query. The query is associated with a list.

List

---

Id
Name
Query

The query generally does not change, but new lists may be added with new queries. This table has hundreds (or thousands) of rows.

The query generates ids from on unit table based on various criteria, and puts that list in a result table.

Result

---

Batch_Id
List_Id
Unit_Id

The Primary Key to the results table would have to be another column, or a composite of the three keys.

The queries are each run on schedule. Possibly multiple times daily, and, it must all be automated.

The list and unit ids are given. The batch id is not. I was thinking of using a sequence, but am not sure how to get that into use.

The procedure can:

SELECT Query INTO The_Query FROM List WHERE Id = variable;

Then:

EXECUTE IMMMEDIATE 'INSERT INTO Result ' || The_Query;

The options as I see them are:

1. Have the query starts from the begining, including SELECT. Then add some identifier to know where to place the sequence id. Then parse it before running it.

Query: SELECT ^^place sequence here^^, Id ... FROM..... The_Query := REPLACE(The_Query, '^^place sequence here^^', The_Sequence_Id);

2) Drop the SELECT from the queries, and add the predicate at runtime.

Query: , Id ... FROM ...
The_Query := 'SELECT ' || The_Sequence_Id || The_Query;

3) Have the query split into two and put them together with the sequence id at runtime.

Query_Predicate: SELECT
Query: , Id ... FROM ...
The_Query := Query_Predicate || The_Sequence_Id || The_Query;

I cannot let the query itself pop an id from the sequence, as then each row will have it's own id, rather than one id for the entire batch.

Brian Received on Wed Jun 27 2001 - 09:53:05 CDT