|
|
|
|
|
With AQT you cannot run a query that accesses tables in more than one database (for instance a table in Oracle and a table in DB2).
When you run a query, AQT runs it against one database and displays the rows that are returned. If your query refers to tables which aren't known to that database, then the query will fail.
This limitation applies to most query tools we are aware of, with the exception of MS Access. With Access you can run multi-database queries; this is discussed below.
AQT has a feature which gives a basic degree of cross-database functionality. This allows you to populate a list of values from either a database query or a csv/excel file. You can then use this list of values in an IN statement against another database.
An example is below:
--aqt setparm,parm=custlist,value=$list(sql="select cust_code from order_details",db=database1)
select * from Customer_Details where cust_code in ($custlist)
The way this works is as follows:
There is further info on this feature at SetParm - $list
MS Access is able to access and join tables from multiple remote databases. It does this by reading the rows from both tables and perform it's own join on the data. If your tables are large, this will be very slow and will take a lot of memory on your PC.
You can use this feature of MS Access within AQT, as follows:
Some databases have Database Gateways, which allow you to connect to a remote database. Tables in the remote database can be accessed as if they were local tables.
If the above options are not feasible, then the only other alternative is to have all the data on a single database. This will involve loading the data from one database onto the other.
Whether this is a feasible option will depend on the quantity of data involved, and the frequency with which it changes (and needs to be reloaded).
The AQT Data Loader can simplify the job of doing this; it can load between different types of databases and can be run in an unattended mode on a regular basis.
