Board index » delphi » Local sql, query on query problem

Local sql, query on query problem

Hi all

I'm experienced at SQL programming (Oracle), and have done a fair bit of
non-database Delphi work but not much Delphi/database.

I'm basically trying to set up what Oracle would call a view. That is, I want
to run a query on the results of another query. I'm using local SQL on Paradox
tables. I'm not in a client/server environment or using ODBC. There must be a
way to do this but I can't see it.

My problem is best illustrated by the following example (the example is
trivial, I've just tried to illustrate my general problem as clearly as
possible. I know there are other, better ways in this case):

query1.sql.clear;
query1.sql.add('select * from "crop.db" where date1 > "1/1/1996"');

query2.sql.clear;
query2.sql.add('select * from ?*?*? where date2 < "12/1/1996"');

query1.open;
query2.open;

The problem:  how the hell do I reference query1, as if it were a table, in
query2???  What do I put instead of ?*?*?

Any help would be greatly appreciated....

Thanks

Chris Jackson

 

Re:Local sql, query on query problem


On Fri, 15 May 1998 00:47:14 +0000, Chris Jackson

Quote
<chris.jack...@marine.csiro.au> wrote:
> I'm basically trying to set up what Oracle would call a view. That is, I want
> to run a query on the results of another query. I'm using local SQL on Paradox
> tables. I'm not in a client/server environment or using ODBC. There must be a
> way to do this but I can't see it.

It's _too_ simple, perhaps -- we often don't see the obvious...

Quote
> query1.sql.clear;
> query1.sql.add('select * from "crop.db" where date1 > "1/1/1996"');
> query2.sql.clear;
> query2.sql.add('select * from ?*?*? where date2 < "12/1/1996"');
> query1.open;
> query2.open;
> The problem:  how the hell do I reference query1, as if it were a table, in
> query2???  What do I put instead of ?*?*?

I think the best (only?) way is to actually _make_ query1 a temporary
table, and then run query2 on that. I'm not certain, but I think Local
SQL supports an "INTO" clause in the SELECT statement; otherwise, use
a TBatchMove with property "Mode" set to "batCopy" in order to create
the destination table from scratch, and give it query1 as the "source"
dataset and your new table name as the "destination" dataset.

If you have to come up with a new table name every time it might get
tricky, but if you don't need it once you've run query2 you could
re-use the same table-name over and over. Set its "database" property
to a directory on the user's local hard disk, then several users can
all run a query of their own. Just make sure that A) the directory
exists on _all_ the users' machines (C:\ ?), and B) that they don't
run several copies of your program simultaneously on one machine.

HTH!

        Christian R. Conrad
--
Proud and sole owner of all opinions (except quotes) expressed above!
======================================================================
     "Wave goodbye to RISC. It's the rotary engine of computing."
            Brad Aisa, in comp.lang.pascal.delphi.advocacy

Re:Local sql, query on query problem


On Fri, 15 May 1998 06:19:50 GMT, christian.con...@hedengren.fi

Quote
(Christian R. Conrad) wrote:

Actually, there is an example of doing a Query WITHIN a query in the
manual.  I believe that you can call the second Query from within the
"WHERE" clause.
Quote
>On Fri, 15 May 1998 00:47:14 +0000, Chris Jackson
><chris.jack...@marine.csiro.au> wrote:

>> I'm basically trying to set up what Oracle would call a view. That is, I want
>> to run a query on the results of another query. I'm using local SQL on Paradox
>> tables. I'm not in a client/server environment or using ODBC. There must be a
>> way to do this but I can't see it.

>It's _too_ simple, perhaps -- we often don't see the obvious...

>> query1.sql.clear;
>> query1.sql.add('select * from "crop.db" where date1 > "1/1/1996"');
>> query2.sql.clear;
>> query2.sql.add('select * from ?*?*? where date2 < "12/1/1996"');
>> query1.open;
>> query2.open;

>> The problem:  how the hell do I reference query1, as if it were a table, in
>> query2???  What do I put instead of ?*?*?

>I think the best (only?) way is to actually _make_ query1 a temporary
>table, and then run query2 on that. I'm not certain, but I think Local
>SQL supports an "INTO" clause in the SELECT statement; otherwise, use
>a TBatchMove with property "Mode" set to "batCopy" in order to create
>the destination table from scratch, and give it query1 as the "source"
>dataset and your new table name as the "destination" dataset.

>If you have to come up with a new table name every time it might get
>tricky, but if you don't need it once you've run query2 you could
>re-use the same table-name over and over. Set its "database" property
>to a directory on the user's local hard disk, then several users can
>all run a query of their own. Just make sure that A) the directory
>exists on _all_ the users' machines (C:\ ?), and B) that they don't
>run several copies of your program simultaneously on one machine.

>HTH!

>        Christian R. Conrad
>--
>Proud and sole owner of all opinions (except quotes) expressed above!
>======================================================================
>     "Wave goodbye to RISC. It's the rotary engine of computing."
>            Brad Aisa, in comp.lang.pascal.delphi.advocacy

Other Threads