Cross Connection Query is not running

Toad Data Point
1

Hi,

I have created a Cross Connection Query in TDA 3.4 (trial version) based on two Oracle databases. It is a query that contains 1 table of database 1 and 1 table of database 2. Both tables are joined with an inner join and I am using a filter.

When I run this query, it starts but it is running the entire day without returning any result.

Are there log files I can check why the qyery is failing?

This is the query:

SELECT FSITEM.ITEM_CODE
, FSITEM.COMPONENT_IND
, FSITEM.DESCRIPTION
, FSITEM.CAS
, FSITEM.COMM_CODE
, FSITEM.STATUS_IND
, FSITEM.APPROVAL_CODE
, FSITEM.HOLD_CODE
, T_PROD_COMP.F_CAS_NUMBER
, T_PROD_COMP.F_COMPONENT_ID
, T_PROD_COMP.F_CHEM_NAME
, T_PROD_COMP.F_TRADE_SECRET_CHEM_NAME
, T_PROD_COMP.F_HAZ_FLAG
, T_PROD_COMP.F_PERCENT
, T_PROD_COMP.F_PERCENT_RANGE
, T_PROD_COMP.F_UNITS
, T_PROD_COMP.F_TRADE_SECRET_FLAG
, T_PROD_COMP.F_SARA_FLAG
, T_PROD_COMP.F_USER_UPDATED
, T_PROD_COMP.F_DATE_STAMP
, T_PROD_COMP.F_ORDER
FROM
G026D_PKGG026D (GPD03), GPD03.GPD03.FSITEM FSITEM
INNER JOIN
P316P_PKGP316P (WERCS).WERCS.T_PROD_COMP T_PROD_COMP
ON (FSITEM.ITEM_CODE = T_PROD_COMP.F_PRODUCT)
WHERE FSITEM.ITEM_CODE IN (‘333’, ‘259’)

2

I was able to run the query by using a workaround :

I have imported the two tables into the Local Storage

Altered both tables and added the PK’s and Indexes (based on the original table setup in the source databases)

Built the query based on the two imported tables in the Local Storage and voila, there is my data!

3

This sounds like a good work around. There is some logging that we can turn on but in your case I think the tables are too large to be efficient when joining them across disparate databases.