Board index » delphi » Chaining TQueries: Querying Paradox TQuery result sets using another TQuery

Chaining TQueries: Querying Paradox TQuery result sets using another TQuery

I need to query the result set of another tquery already opened. The tables
are paradox and must remain so.

Here is the SQL for the 1st TQuery ( I'll call it "QryDrugCount"):

    SELECT DISTINCT organism, DrugAbbr, SUM(NCCLStotal) AS Total
    FROM "BugTable.db"
    GROUP BY Organism, DrugAbbr
    HAVING SUM(NCCLStotal) > 10
    ORDER BY Organism, DrugAbbr

(I'll call the result set "DrugCount.db" even though it doesn't actually
exist as a table.)

"DrugCount.db" looks like this:

Organism     DrugAbbr     Total
-----------------------------------------------
C.freundii     Ak                  60
C.freundii     Aug                60
C.koseri       A/S                 20
C.koseri       Ak                  40
C.koseri       Aug                40

Now, I want to do another GROUP BY on the result (I'll call the 2nd Tquery
"QryIsolateCount").

    SELECT DISTINCT Organism, MAX(Total) as Isolates
    FROM {!!DrugCount.db!!}                                { doesn't really
exist!!}
    GROUP BY Organism

This would give me the result:

Organism      Isolates
---------------------------------
C.freundii      60
C.koseri        40

That is, it would if I had a table called "DrugCount.db"!  But, since it is
the result set of a previous query, it doesn't!
(NOTE: I know I could change the first query and skip the whole issue, but
right now I'm more interested in how to chain TQuerys.)

I tried setting the data source property of "QryIsolateCount" to
"DSQryDrugCount" (the datasource of the first query) and using parameters
for the fields, but I can't get past the FROM clause.  (Error msg: "Table
does not exist")

How do I use the result set of one tquery as the source for a 2nd Tquery
using Paradox tables!

 

Re:Chaining TQueries: Querying Paradox TQuery result sets using another TQuery


Hello Alan,

I don't think you can query a query in your context.  What you can do,
though, is to use the TBatchMove component to save the contents of the Query
to another Paradox table, then issue a second query on that.

Make the new Paradox table sit on a local drive for speed, and if you use
the BatchMove.Mode := batCopy way of doing things, BatchMove will create the
Paradox table for you, so you don't have to worry about it existing
beforehand.

Hope this helps.

Paul Harding.

Quote
Alan Aipperspach wrote in message <87hmir$6...@bornews.borland.com>...
>I need to query the result set of another tquery already opened. The tables
>are paradox and must remain so.
>.......

Re:Chaining TQueries: Querying Paradox TQuery result sets using another TQuery


If you choose to do this, you might like to look at the BatchMove method of
TTable which will eliminate the need for a separate TBatchMove component.

HTH,

Conor

Quote
Paul Harding <p...@paulharding.fsnet.co.uk> wrote in message

news:87kbhp$t11$1@news7.svr.pol.co.uk...
Quote
> Hello Alan,

> I don't think you can query a query in your context.  What you can do,
> though, is to use the TBatchMove component to save the contents of the
Query
> to another Paradox table, then issue a second query on that.

> Make the new Paradox table sit on a local drive for speed, and if you use
> the BatchMove.Mode := batCopy way of doing things, BatchMove will create
the
> Paradox table for you, so you don't have to worry about it existing
> beforehand.

> Hope this helps.

> Paul Harding.

> Alan Aipperspach wrote in message <87hmir$6...@bornews.borland.com>...
> >I need to query the result set of another tquery already opened. The
tables
> >are paradox and must remain so.
> >.......

Re:Chaining TQueries: Querying Paradox TQuery result sets using another TQuery


I found the answer accidently while browsing the borland web page.

To create temporary tables for use by further queries, you must use either
tbatchmove or Ttable.batchmove. Ttable.batchmove moves data from any dataset
(including queries) into the table it is a method of.

I found this in http://community.borland.com/article/0,1410,20563,00.html
Titled: Top 10 tricks for Delphi and C++Builder VCL Database Developers.

Quote
Alan Aipperspach wrote in message <87hmir$6...@bornews.borland.com>...
>I need to query the result set of another tquery already opened. The tables
>are paradox and must remain so.

>Here is the SQL for the 1st TQuery ( I'll call it "QryDrugCount"):

>    SELECT DISTINCT organism, DrugAbbr, SUM(NCCLStotal) AS Total
>    FROM "BugTable.db"

...
Quote
>How do I use the result set of one tquery as the source for a 2nd Tquery
>using Paradox tables!

Re:Chaining TQueries: Querying Paradox TQuery result sets using another TQuery


If I understand it correctly you want to base a query on another query. I've
never tried this with TQuery components, but it will work with the help of
database desktop however if you do the following :
1. Create a SQL file containing your first query in the DB you're bugtable's
in. Call it for example 'MySQL.sql'.
2. Get the TQuery component loaded with the following SQL statement
    SELECT DISTINCT Organism, MAX(Total) as Isolates
    FROM 'MySQL.sql'
    GROUP BY Organism
This works well most of the time. I've used this approach lots of times with
Pdox. Always worked!

Bart

Alan Aipperspach heeft geschreven in bericht
<87hmir$6...@bornews.borland.com>...

Quote
>I need to query the result set of another tquery already opened. The tables
>are paradox and must remain so.

>Here is the SQL for the 1st TQuery ( I'll call it "QryDrugCount"):

>    SELECT DISTINCT organism, DrugAbbr, SUM(NCCLStotal) AS Total
>    FROM "BugTable.db"
>    GROUP BY Organism, DrugAbbr
>    HAVING SUM(NCCLStotal) > 10
>    ORDER BY Organism, DrugAbbr

>(I'll call the result set "DrugCount.db" even though it doesn't actually
>exist as a table.)

>"DrugCount.db" looks like this:

>Organism     DrugAbbr     Total
>-----------------------------------------------
>C.freundii     Ak                  60
>C.freundii     Aug                60
>C.koseri       A/S                 20
>C.koseri       Ak                  40
>C.koseri       Aug                40

>Now, I want to do another GROUP BY on the result (I'll call the 2nd Tquery
>"QryIsolateCount").

>    SELECT DISTINCT Organism, MAX(Total) as Isolates
>    FROM {!!DrugCount.db!!}                                { doesn't really
>exist!!}
>    GROUP BY Organism

>This would give me the result:

>Organism      Isolates
>---------------------------------
>C.freundii      60
>C.koseri        40

>That is, it would if I had a table called "DrugCount.db"!  But, since it is
>the result set of a previous query, it doesn't!
>(NOTE: I know I could change the first query and skip the whole issue, but
>right now I'm more interested in how to chain TQuerys.)

>I tried setting the data source property of "QryIsolateCount" to
>"DSQryDrugCount" (the datasource of the first query) and using parameters
>for the fields, but I can't get past the FROM clause.  (Error msg: "Table
>does not exist")

>How do I use the result set of one tquery as the source for a 2nd Tquery
>using Paradox tables!

Other Threads