How do I perform a Union in Paradox 5.0

[Guest_imported]

I want one query to select the matching records, on Branch and Account Number, from one table in two other tables. I need to do a Union, but it does not appear that Paradox supports this. Here is what I have:
SELECT DISTINCT D1.BranchNbr, D1.AccountNbr
FROM "BTXREF.DB" D, "INVENTRY.DB" D1
WHERE
(D1.BranchNbr = D.BranchNbr)
AND (D1.AccountNbr = D.AccountNbr)
ORDER BY D1.BranchNbr, D1.AccountNbr

UNION

SELECT DISTINCT D1.BranchNbr, D1.AccountNbr
FROM "BTXREF.DB" D, "TRANSIT.DB" D1
WHERE
(D1.BranchNbr = D.BranchNbr)
AND (D1.AccountNbr = D.AccountNbr)
ORDER BY D1.BranchNbr, D1.AccountNbr

 

[footpad]

mcybula,

According to the latest LOCALSQL.HLP file (provided with BDE 5.1.1), this is supported. It may not have been supported in the version of BDE provided with Paradox 5.0x.

You did not say what your results were, but if the query runs and does not return rows, try adding the word ALL after UNION.

Alternatively, if your BDE does not support this, can always try a QBE query like this:

var
   qry Query
endVar

   qry = QUERY

   BTXREF.DB | BranchNbr | AccountNbr |
             | _b1, b2   | _a1, _a2   |

   INVENTRY.DB | BranchNbr | AccountNbr |
   check       | _b1       | _a1        |

  TRANSIT.DB | BranchNbr | AccountNbr |
   check     | check _b2 | check _a2  |

   endQuery
   ; additional code as needed

I should point out that this is off the top of my head, so you may need to fiddle with the actual presentation a bit, but the idea is there.

There are a couple of "tricks" in that:

1. Placing CHECK underneath the table name automatically selects all fields in the table.

2. Adding additional CHECK keywords (as I did with TRANSIT.DB) toggles the checked state of the field. In this case, it "unchecks" the BranchNbr and AccountNbr fields in Transit, which in turn prevents two sets of these fields in your ANSWER table.

Also, why not use a standard SQL join?

Hope this helps...

-- Lance