In my project I had to query the orders by order date and then sort them with the latest order date showing up first. The issue was when an Order got Invoiced it went into the history file which was kind of similar but had different field names. So the solution was to create a view using SQL union between the two tables and do your query on a single view. Here is how ...
Open Order Table
Field File Type Length Scale OID OPENO NUMERIC 10 OCUST OPENO CHARACTER 10 ONAME OPENO CHARACTER 30 ODATE OPENO NUMERIC 8 OAMOUNT OPENO NUMERIC 11 2
History Order Table
Field File Type Length Scale HID HISTO NUMERIC 10 HCUST HISTO CHARACTER 10 HNAME HISTO CHARACTER 30 HDATE HISTO NUMERIC 8 HAMOUNT HISTO NUMERIC 11 2
Union between the Open and History tables
Create view MyOrders as ( select oid, ocust, oname, odate, oamount from openo union all select hid oid, hcust ocust, hname oname, hdate odate, hamount oamount from histo)
Now its easy to query the MyOrders view with any selection criteria or order by ...
Select * from MyOrders where odate between 20120222 and 20120223