Board index » delphi » SQL views / dataset

SQL views / dataset

Can you use a TQuery's dataset as the source for another query without
saving it first as a paradox table on disk ?

for eg:

SELECT CustomerName
FROM Customer.db
WHERE CustomerName = Query1.CustomerName

I know that the WHERE statement is wrong but is there a way to use a query's
dataset as a lookup.

I believe this is possible is ACCESS'97

Thanks,

Vikas David
Delsoft
vi...@newton.dialix.com.au

 

Re:SQL views / dataset


Yes you can do this.  You just have to make sure you are on the record you
want to be on when you do your query.  The statement in delphi code would be
something like this:

YourQuery.Sql.Add('SELECT CustomerName' +
     ' FROM Customer.db' +
' WHERE CustomerName = :CustomerName ' );
YourQuery.ParamByName('CustomerName').AsString :=
    Query1.FieldByName('CustomerName ').AsString;

This will give you the CustomerName value of the current record of Query1.
If you want to use all of the records in Query1, simply loop through the
dataset and build an "in" statement:

Where CustomerName in(
here is where you have to loop through the dataset and get the customer
names you want and put them inside parens, quoted and separated by commas.
--
Michael Glatz
mgl...@briefcase.com

Quote
Vikas David wrote in message <6t8p61$ba...@forums.borland.com>...
>Can you use a TQuery's dataset as the source for another query without
>saving it first as a paradox table on disk ?

>for eg:

>SELECT CustomerName
>FROM Customer.db
>WHERE CustomerName = Query1.CustomerName

>I know that the WHERE statement is wrong but is there a way to use a
query's
>dataset as a lookup.

>I believe this is possible is ACCESS'97

>Thanks,

>Vikas David
>Delsoft
>vi...@newton.dialix.com.au

Re:SQL views / dataset


It sounds like what you are looking for is subqueries (another select in the
where clause).  For example:

select * from orders where TotalAmount > (select avg(TotalAmount) from
orders)

--
Bill Todd
(Sorry but TeamB cannot answer questions received via email)
(Remove nospam from my email address to contact me for any other reason)

Other Threads