How to create and query a SQL view over multiple database using multi-database connectivity and Progress Developer Studio for OpenEdge or OpenEdge Architect?

In OpenEdge Release 10.1B and later, ODBC and JDBC clients can connect to multiple databases that reside on the same host.  Although the following steps used the OpenEdge Architect and JDBC connections, the same SQL-92 script may be used with other ODBC and JDBC connections and clients:

1. Start a proenv session:

Start > Programs > OpenEdge > Proenv

2.  Create three copies of the sports2000 database:

prodb firstdb sports2000
prodb seconddb sports2000
prodb thirddb sports2000

3.  Serve all three databases:

proserve firstdb -H localhost -N tcp -S 12345
proserve seconddb -H localhost -N tcp -S 23456
proserve thirddb -H localhost -N tcp -S 34567

4.  Start an OpenEdge Architect session:

Start > Programs > OpenEdge > OpenEdge Architect

5.  In The DB Navigator Perspective Connections View, Create and test the three Connection Profiles.

6.  In The DB Navigator Perspective Connections View, open connection for the firstdb.

7.  File > New > Other > DB Navigator > SQL Editor > MultiDBViewExample.sql

8. Paste and run the following script in the SQL Editor:

CONNECT 'C:\OpenEdge\WRK101B\seconddb' AS CATALOG second;
CONNECT 'C:\OpenEdge\WRK101B\thirddb' AS CATALOG third;

CREATE VIEW ThreeDBView (FieldFromFirstDB, FieldFromSecondDB, FieldFromThirdDB) AS
(
SELECT
firstdb.Pub.Customer.CustNum,
second.Pub.Order.OrderNum,
third.Pub.Orderline.itemnum
FROM
firstdb.Pub.Customer,
second.pub.order,
third.pub.orderline
WHERE
firstdb.Pub.Customer.CustNum = second.Pub.Order.CustNum AND
second.Pub.Order.OrderNum = third.Pub.Orderline.OrderNum
);

9. File > New > Other > DB Navigator > SQL Editor > ExecuteQueryAgainstView.sql

10. Paste and run the following script the SQL Editor:

SELECT * from ThreeDBView;

OpenEdge Architect was renamed "Progress Developer Studio for OpenEdge" in release 11.0. This article applies to the product under both names.

The CONNECT statements, executed within sqlexplorer, must refer to file paths to the databases. sqlexplorer does not allow network connections for connecting to the databases. 

Users utilizing the view must have permissions that allow accessing of any underlying databases tables, accessed in the view.

References to Other Documentation:
Progress Article(s):

 SQL-92: How to create and execute an SQL-92 query against Multiple databases using the OpenEdge Architect?
 Quick Guide to setting up Multi Database ODBC / JDBC connectivity