Help with SQL Statement applied to Paradox tables 
Author Message
 Help with SQL Statement applied to Paradox tables

Hi,

Using delphi 4 C/S, Paradox 7.0, BDE 5.10...

I having problems trying to get an SQL statement to work across
multiple Paradox tables (mainly due to my limited knowledge of SQL).
Here's what I'm trying to achieve...

I have 4 tables (Assessments, Assessors, Customers & Invoices).
As the Assessments table was getting rather large, I split the table
into archive directories (years), so 1999 Assessments are in a "1999"
directory, 2000 Assessments are in a "2000" directory, etc, etc...

Now, I want to generate sales figures based on information from all 4
tables. This all seems to work correctly if I only access 1
Assessments table.

This is roughly the statement I'm using here:

select  (all fields from Invoices),
        (2 fields from Assessors),
        (2 fields from Customers),
        (6 fields from Assessments)
from Invoices, Assessors, Customers, Assessments
where blah, blah, blah...

The problem comes when I try to access more than 1 Assessments tables,
there's a lot of network activity, lots of disk crunching and then
after about 10 minutes, I get a "Disk Full" error message.
I have approx. 2 GB of disk space and the Assessments Tables are
approx. 600 MB each.

This is roughly the statement I'm using here:

select  (all fields from Invoices),
        (2 fields from Assessors),
        (2 fields from Customers),
        (6 fields from Assessments),
        (6 fields from \1999\Assessments),
from Invoices, Assessors, Customers, Assessments
where blah, blah, blah...

Am I going about this the right way? If there is a better way to do
this, I'd really like know. If there are any good SQL reference sites
around, I'd really like to know that too (if it's not too much
trouble)...

Anyway, thanks and my appologies for being so verbose.

Regards,

Robbie...



Wed, 28 Jan 2004 19:32:49 GMT  
 Help with SQL Statement applied to Paradox tables
First off, check LocalSQL.hlp in your BDE directory for
information on what SQL will be supported for Paradox
tables.  For general information on SQL, there are lots
of places you can go; you might start with the page of
links at http://delphi.about.com/

If you have any experience working with Paradox or
Database Desktop, remember that DD allows you to build
a Paradox-style QBE query, then do "Query-Show SQL"
to convert it to a SQL statement that will work. It may
not be the most optimized statement, but it's something
to start with.

Paradox tables can actually be quite large, so long as
the block size is large enough. You may want to consider
combining your tables again if you have need of querying
on all the data simultaneously.

No matter how you write your SQL, you have no control over
just how the BDE will decide to extract the data, and what
temporary tables it will create. So, what I would do in
your situation is run separate queries on each table,
then combine the results.  Here TBatchMove will be your
friend.
--
Rick Carter

Chair,   Paradox/Delphi SIG,   Cincinnati PC Users Group



Thu, 29 Jan 2004 01:31:22 GMT  
 Help with SQL Statement applied to Paradox tables
Hi Richard,

Thanks for the quick response, I'll give those suggestions a go...

Regards,

Robbie...

Quote:

>First off, check LocalSQL.hlp in your BDE directory for
>information on what SQL will be supported for Paradox
>tables.  For general information on SQL, there are lots
>of places you can go; you might start with the page of
>links at http://delphi.about.com/

>If you have any experience working with Paradox or
>Database Desktop, remember that DD allows you to build
>a Paradox-style QBE query, then do "Query-Show SQL"
>to convert it to a SQL statement that will work. It may
>not be the most optimized statement, but it's something
>to start with.

>Paradox tables can actually be quite large, so long as
>the block size is large enough. You may want to consider
>combining your tables again if you have need of querying
>on all the data simultaneously.

>No matter how you write your SQL, you have no control over
>just how the BDE will decide to extract the data, and what
>temporary tables it will create. So, what I would do in
>your situation is run separate queries on each table,
>then combine the results.  Here TBatchMove will be your
>friend.



Thu, 29 Jan 2004 06:26:20 GMT  
 Help with SQL Statement applied to Paradox tables


Quote:
>This is roughly the statement I'm using here:

>select         (all fields from Invoices),
>       (2 fields from Assessors),
>       (2 fields from Customers),
>       (6 fields from Assessments),
>       (6 fields from \1999\Assessments),
>from Invoices, Assessors, Customers, Assessments
>where blah, blah, blah...

You could try using a UNION to collect data from all of the Assessments
tables.  So your example above would become:

SELECT (all fields from Invoices),
       (2 fields from Assessors),
       (2 fields from Customers),
       (6 fields from \1999\Assessments)
FROM   Invoices, Assessors, Customers, \1999\Assessments
UNION
SELECT (all fields from Invoices),
       (2 fields from Assessors),
       (2 fields from Customers),
       (6 fields from \2000\Assessments)
FROM   Invoices, Assessors, Customers, \2000\Assessments

Not sure if this would help, I suppose it depends on how the driver
actually processes the SQL, but if each section of the UNION is
processed separately by the driver, then joined afterwards to create the
result set, it might lighten the load a bit.

HTH :)
--
Toby



Tue, 03 Feb 2004 01:38:55 GMT  
 Help with SQL Statement applied to Paradox tables
Hi Toby,

I just got back from OS, thanks for your response...

I tried it and it **nearly** works...

I should be getting 409 records, I actually get 818 records. The first
409 records have information from only the invoices table, the last
409 records contain the correct information.

Can you see anything I'm doing wrong???

This is exactly what I did:

SELECT

\Database_S\Archives\2000\Assessments."JobNo Index",
\Database_S\Archives\2000\Assessments."JD Job No.",
\Database_S\Archives\2000\Assessments."JD Assessor Code",
\Database_S\Archives\2000\Assessments."CI Claim No",
\Database_S\Archives\2000\Assessments."ID Insured",
\Database_S\Archives\2000\Assessments."AD Policy No",
\Database_S\Archives\2000\Assessments."VD Vehicle Registration",
\Database_S\Archives\2000\Assessments."CI Code",
\Database_S\Archives\2000\Assessments."AD Assess On",
Invoices."JD Job No",
Invoices."Paid",
Invoices."Paid Date",
Invoices."Sent",
Invoices."Invoice Number",
Invoices."Date",
Invoices."I1Amount",
Invoices."Sub Total",
Invoices."GST",
Invoices."Reminders",
Invoices."Rem Date",
Assessors."Assessor Code",
Assessors."Assessor",
Customers."Customer Code",
Customers."Company"

FROM

Invoices I1

FULL OUTER JOIN \Database_S\Archives\2000\Assessments A0
ON (I1."JD Job No" = A0."JD Job No.")
FULL OUTER JOIN Assessors As1
ON (As1."Assessor Code" = A0."JD Assessor Code")
FULL OUTER JOIN Customers C1
ON (C1."Customer Code" = A0."CI Code")

WHERE

I1."Sent" = "True" and I1."Paid" = "NP"

UNION

SELECT

Assessments."JobNo Index",
Assessments."JD Job No.",
Assessments."JD Assessor Code",
Assessments."CI Claim No",
Assessments."ID Insured",
Assessments."AD Policy No",
Assessments."VD Vehicle Registration",
Assessments."CI Code",
Assessments."AD Assess On",
Invoices."JD Job No",
Invoices."Paid",
Invoices."Paid Date",
Invoices."Sent",
Invoices."Invoice Number",
Invoices."Date",
Invoices."I1Amount",
Invoices."Sub Total",
Invoices."GST",
Invoices."Reminders",
Invoices."Rem Date",
Assessors."Assessor Code",
Assessors."Assessor",
Customers."Customer Code",
Customers."Company"

FROM

Invoices I1

FULL OUTER JOIN Assessments A1
ON (I1."JD Job No" = A1."JD Job No.")
FULL OUTER JOIN Assessors As1
ON (As1."Assessor Code" = A1."JD Assessor Code")
FULL OUTER JOIN Customers C1
ON (C1."Customer Code" = A1."CI Code")

WHERE

I1."Sent" = "True" and I1."Paid" = "NP"

ORDER BY

Assessments."JobNo Index"



Mon, 16 Feb 2004 19:43:18 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Paradox DDL Scripts, multiple SQL Statements

2. SQL Statement order with Paradox

3. D1: Sql Statement to add a field to table

4. Creating table with referencial integrity from sql-statement...

5. Help on storing SQL Table result to a Paradox file

6. Help with BDE and SQL Update Dates in Paradox Tables

7. Help with SQL Date functions with Paradox Tables in WHERE clause

8. joining a paradox table to an MSAccess table in an SQL query

9. Q - SQL / D2 / Paradox Tables / SQL Syntax question!!

10. Help with SQL Statement

11. HELP:SQL statement fails

12. Help needed with SQL - Update Statement

 

 
Powered by phpBB® Forum Software