Linking Datasets

[syi129]

Hi

I have got a question; I have a few datasets that return the same fields just with different criteria in the SQL. I want to do a link from those dataset to a new dataset that contains a select statement. However the only thing I can see is a master detail relationship which goes off one record at a time. I am looking for a data pipeleine behaviour where it queries all of the information at once. It would have been great if I use a temp table built up in memory or if I could have passed in an array DML but unfortunately that does not seem to work with select statements. Any suggestions would be appreciated.

thanks

 

[Marco Kalter]

I'm not quite sure what you mean. Can you provide an example?

 

[syi129]

ok, say I have 2 queries

with Query1 do
begin
SQL.Add('SELECT FirstName, LastName from table1......')
Execute;
end;

I want Query2 to select all of the records where the firstname and lastname where returned from Query1.

i.e
with Query2 do
begin
SQL.Add('SELECT * from table2 where FirstName=:FirstName and LastName=:LastName');
SetVariable('FirstName', Query1.Field('FirstName'));
SetVariable('LastName', Query1.Field('LastName'));

Execute;
end;

I am current doing the query below but the hit on table1 is terrible because it is a large table and I would rather not have to add table1 into Query2 because each time I want to refresh the Query2, it requeries table1.

with Query2 do
begin
SQL.Add('SELECT table2.* from table1, table2 where table1.FirstName=table2.FirstName and table1.LastName=table2.LastName ...........');
Execute;
end;

 

[Marco Kalter]

I don't immediately see a better solution. Are the 2 columns properly indexed on both tables and are the table statistics up-to-date?