Cross database queries

Starting from version 9.0, WinSQL supports cross database queries, which is accomplished by running an embedded Named Query.

Consider the following example:

• You have two types of database systems in your company: Billing database runs on Oracle and Order Processing runs on IBM DB2.
• You want to find out the orders that have not been billed yet.
• Your goal is to find records in the Orders table (in Oracle), that do not exist in the Billing table (in DB2)

Steps to accomplish this task

• Create a Named Query against your Oracle table that returns records in billing that have been paid. The query can look something like:
  
  -- Query against an Oracle Table
  select custId
  from Billing
  where PaidStatus = 'Y'
  
  
• Let's name this query Paid Customers. Once the query is saved you will see the Named query in the right hand side panel.
• Establish connection to your DB2 database
• Write a query like following.
  
  -- Query against a DB2 table
  select *
  from Orders
  where custId not in ()
  
  Notice the two parenthesis in after the NOT IN clause. If you can somehow paste the customer IDs from the table in Oracle, you will get your final result.
• Now drag the Paid Customer query that appears on the right hand side and drop it between the two parenthesis. This will create a special function holder in WinSQL that accepts a Named Query
• The final query will look something like:
  
  -- Query against a DB2 table
  select *
  from Orders
  where custId not in ( wnsFunc_RunNQ("Paid Customer") )
  
  
• When you run this query, WinSQL will first run the embedded query against Oracle and replace the function with the actual results.
• Finally, the query is run against the DB2 table to retrieve the desired output.

Caveats

The following points should keep in mind when running cross database queries.

• Only the first column is used in the Named Query. If the query returns more than one columns every column but the first one will be ignored.
• WinSQL builds the outer query at run-time by pasting the results of the embedded query. If the data type needs quotes, WinSQL will automatically create a quoted string. For example:
  
  -- Query against a DB2 table
  select *
  from Orders
  where Region in ( wnsFunc_RunNQ("New Regions") )
  
  If the data type of the result column is VARCHAR, the final query will look like:
  
  -- Query against a DB2 table
  select *
  from Orders
  where Region in ( 'North', 'Pacific' )
• WinSQL builds the final query in memory and submits it to the database. Therefore, the number of rows returned by the embedded query is limited to the memory as well as the capabilities of the target database.