Kbase P143663: An example of a multi-database SQL Query
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
3/26/2009 |
|
Status: Unverified
GOAL:
An example of a multi-database SQL Query
FACT(s) (Environment):
All Supported Operating Systems
OpenEdge 10.1B
OpenEdge 10.1C
OpenEdge 10.2x
FIX:
For purposes of this example the SQL client will be connected to two accounting databases.
The catalog name for the first database is gledger.
The catalog name for the second database is actpay.
If the objective was to execute a query statement against the accounts table of the pub schema in the gledger catalog and the accounts table of the pub schema in the actpay catalog the following statement would be used to perform the query of both catalogs:
SELECT actpay.pub.accounts.name FROM gldger.accounts, actpay.accounts
WHERE actpay.pub.accounts.closed = ?n? AND gledger.pub.accounts.closed = ?n?;
In the first line of the above select statement immediately following the SELECT command:
Actpay is a reference to the catalog name of the second database.
Pub is the schema name of the second database.
Accounts is the table name of the second database.
Name is the field name of the accounts table of the second database.
The FROM clause distiguishes the two catalogs and table names where the data will be pulled from.
The WHERE clause also uses the catalog names when referencing the tables that are part of specific catalogs (databases).