ColdFusion Magic:- Join between oracle and sql server Using resultset

Yes, it is possible to JOIN result sets from different datasources

One unique use for Query of Queries is to JOIN recordsets from separate queries. By extension, this means you can JOIN recordsets from different datasources as well.

Let’s assume that my datasource for customers is an Oracle database, but the database for customer orders is SQL Server. I realize this is a bit contrived, but we all know how strange the corporate operating environment can be. Using Query of Queries, you can run a JOIN on the two recordsets.

In this example, I’m grabbing orders for a specific customer. First, we’ll look at the getCustomerOrders query, which will provide the second recordset that we’ll use in our JOIN along with the getCustomers recordset:


select
orderID,
customerID,
orderAmount
from
customerOrders

This would produce a recordset like the one shown in Figure 1.

JOIN query(Figure 1)

Now, let’s JOIN these separate result sets:


select
getCustomers.customerID,
getCustomers.customerName,
getCustomerOrders.orderID,
getCustomerOrders.orderAmount
from
getCustomers, getCustomerOrders
where
getCustomerOrders.customerID = getCustomers.customerID
AND getCustomerOrders.customerID = 91

The resulting recordset can be seen in Figure 2.

JOIN Query of Query(Figure 2)

This ability to relate records in separate queries can be a useful approach to certain programming challenges.