Board index » delphi » please help me on MULTIPLE TABLE QUERIES

please help me on MULTIPLE TABLE QUERIES

I've been asked to create an historical database to log time - related
events.
Its structure is the following:

Fields: [dCode, TInteger] [dValue, TFloatNumber] [dTime, TDateTime]
Indexes: any

What I can not get working is an SQL statement of this kind:

SELECT * FROM "19980926.DBF","19980927.DBF", ...... WHERE ((DCODE = 1232212)
OR
(DCODE = 1231232)) AND (DTIME >= "26/09/98 12:43.00") AND (DTIME <=
"27/09/98 17:43.32")

I have been asked to split the database using a different file for every
day.
So I've written some code to create the appropriate SQL statement having as
inputs a time interval
and  other parameters.
The sql I wrote still seems to me to be written in the proper way... but
something strange has keep on happening.

First problem:
If the user asks to include in the query a bunch of days whose files do not
exist, the correspondent names
are not included in the sql string. The time interval I specify indeed
reflect what the user has asked.
It happens the dataset is given back only if user asks for days that are
NEAR to the one whose files exist.
For example suppose I have data files regarding just only today and
yesterday.
The query works only if I ask in the last part of the SQL a time interval
that is not TOO far from yesterday
and today. Otherwise what I get back it's an empty dataset. Something quite
{*word*217} I think.
I hope you've caught it on: it happens even if I specify a time interval
that includes the days for which tables exist.

Second problem: the speed.
In one of the fortunate cases the query has given me something it has taken
something like ten minutes
producing two strange .DBF files of 68Mbytes (!). This has happened working
on two data table of about
1000 records each.

Currently I've restricted the query range to ONE day using TTable class
but... this is not a solution.

Thanks for your precious help
   Michele Rossi

[the return address is RIGHT]

 

Re:please help me on MULTIPLE TABLE QUERIES


Quote
On Mon, 28 Sep 1998 22:09:16 +0200, "Michele" <ross...@tin.it> wrote:
>I've been asked to create an historical database to log time - related
>events.
>Its structure is the following:

>Fields: [dCode, TInteger] [dValue, TFloatNumber] [dTime, TDateTime]
>Indexes: any

>What I can not get working is an SQL statement of this kind:

>SELECT * FROM "19980926.DBF","19980927.DBF", ...... WHERE ((DCODE = 1232212)
>OR
>(DCODE = 1231232)) AND (DTIME >= "26/09/98 12:43.00") AND (DTIME <=
>"27/09/98 17:43.32")

Hi there,

I'm not sure I understand everything you need to do but I'm gonna give
you some input anyway. ;)

I don't think you can do what you want to do using SQL. The above SQL
statement you're writing are going to have "funny" results. This
result set however is 100% correct if you analyse what you've asked
for.

You will receive a result set where all the records from the first
table covered by the where clause will be included. The problem is
that these records will appear in your result set (1 * number of
records in table2). I've tested this. This happens because of the way
joins are handled by the SQL server.

I suggest you try the following:
1. Create an empty temporary paradox table with the same structure as
those you're running the query against.
2. Run the query against each individual table seperatetly.
3. Use a TBatchMove component to copy the individual result sets to
your temporary table.
4. Now link to the temporary table which contains all the records you
need.

This can be achieved with not to much code writing making use of a
while ... do loop.

Hope this helps.

Gerhard

Re:please help me on MULTIPLE TABLE QUERIES


You could also use a simple UNION statement.  You must be aware that what
you are doing is very unorthodox because every day you will get on extra
UNION statement.  Try keeping everything in one table and do a daily export
(tBatchMove) to your daily back tables.  Performance will be much better.
--
Van den Driessche Willy

Willy.Van.den.Driess...@Skynet.be

Other Threads