Select From (Select From...)...

[novice001]

Hi All!
Is it possible in Paradox to use SQL queries with subqueries in the form of

Select [...]
From (Select [...] From [...] )
Where [...]

I believe this is standard SQL syntax, but it seems to be unsupported in Paradox.

What I'm trying to do is the following (maybe you have other ideas how to do that). I have table of Orders, and for each order: table with Amounts that are to be paid and table with Payments. I want to find the orders that are not paid in full, i.e. for which sum(Payment)<Sum(Amount)

Here's the code that gives Order No, sum(Payment) and sum(Amount):

SELECT Orders.&quot;Order No&quot;, SUM(Orditem.&quot;Amount&quot AS SUM_OF_Amount, SUM(PAYMENTS.&quot;Payment&quot AS SUM_OF_Payment
FROM &quot;:data:Orders.DB&quot; Orders
INNER JOIN &quot;:data:Orditem.DB&quot; Orditem
ON (Orders.&quot;Order No&quot; = Orditem.&quot;Order No&quot
INNER JOIN &quot;:dataAYMENTS.DB&quot; PAYMENTS
ON (Orders.&quot;Order No&quot; = PAYMENTS.&quot;Order No&quot
GROUP BY Orders.&quot;Order No&quot;

I can save this table to file and do:

Select * from MyTable
Where Sum_Of_Amount<Sum_Of_Payment

But this is not &quot;nice&quot;, as it takes intermediate step. I believe there's a way to do it without saving results and quering them again - like querying the subquery.

I tried to put the first query in the second instead of MyTable, but it gives syntax error. Am I doing something wrong?

Thank You

 

[footpad]

novice001,

Sadly, no. Rather, not in the extensive way you may be used to with other servers. BDE's LocalSQL (the engine used to process Paradox and dBASE tables) has always been documented as a subset of ANSI SQL, so some limitations are to be expected. I'm afraid that sub-selects are one of these limitations.

Having said that, I have seen some cases where simple sub-selects do work against local Paradox tables. For example, the following query worked in the BDE provided with Delphi 3.0x:

   delete
   from
      "ORDERS.DB" o
   where
      o.Customer_Key not in
      ( select c.Customer_Key from "CUSTOMER.DB" c )

I used it to remove orphaned detail records, which is a problem that should never occur. I've seen related ones that update a foreign key based on changes to the master key.

Now, I've not tried to use this in more recent versions, so I can't say it will work. If it works, then I suspect it does so primarily because Borland added the capability for other servers and it just happened to work against Paradox tables as well. I can't say whether this is supported behavior or not. So, caveat implementor.

In general, the &quot;official&quot; stand for the type of operation you're trying to perform has been to separate the process into multiple queries.

You will need to create the ANSWER table, though. Because of that, I'd break the process into three steps:

1. A small query to return just the foreign keys and the sums.

2. A second query against the results to return the sums you're interested, e.g. those that've overpaid.

3. A third and final query to perform the joins.

This should perform more quickly than the massive query you start with. I know this seems less elegant, but I've found that large queries can take considerable time to process and that multiple small queries can reduce network traffic, thereby improving performance.

Hope this helps...

-- Lance