Multiple Schemas in Cross Connection Query

Toad Data Point
1

Hello,

I am using Toad Data Point 3.5.0.2936 and I am having trouble with the Cross Connection Query Editor. I’ve used cross connection queries before to join Excel spreadsheets with tables from my Oracle database without issue. I have to schemas available to me within the Oracle Database.

I am trying to run a query that uses the Excel spreadsheet and both schemas within my Oracle database. When I do so I get an error message saying that the table in one of the schemas does not exist. I’ve also tried running the query without the Excel spreadsheet an I get the same error. If there a limitation within the cross connection query tool that disallows using multiple schemas in the same DB? If so, is there any way around this?

2

Are you using the Pro version? You should be able to do this. Can you post the query that is giving you this error? Are you using the Query Builder to build the Cross-Query. It is pretty good about using the correct fully qualified syntax.

3

Hi Debbie,

I am using the Pro version of Toad Data Point. I can replicate the error using the Query Builder. Here is the type of query that I am having trouble with:

SELECT ISU.VENDOR,

ISU.DOCUMENT,

STF.PO_NAME AS SHIP_TO_NAME,

STF.ADDR1 AS ADDRESS_1,

STF.STATE_PROV AS STATE,

STF.CITY_ADDR5 AS CITY,

STF.POSTAL_CODE AS POSTAL_CODE,

CONCAT(ISU.VENDOR, ISU.MANUF_NBR) AS ITEM,

SUBSTR(ISU.ITEM_DESCR, 10, 30) AS DESCRIPTION,

ISU.TRANS_DATE AS SHIPMENT_DATE,

ISU.UNIT_COST AS CUSTOMER_PRICE,

ISU.QUANTITY * -1 AS QUANTITY_SHIPPED,

POVL.CURR_NET_CST AS LIST_PRICE,

POVL.UOM AS PRICING_UOM,

POVL.UOM_MULT AS MULTIPLIER,

POVL.VEN_AGRMT_REF AS NOVATION_CONTRACT,

ISU.MANUF_NBR AS MFG_#,

(ISU.QUANTITY * -1) / POVL.UOM_MULT AS BULKQTY,

ISU.UNIT_COST / POVL.UOM_MULT AS SALESUNITPRICEEA,

ISU.UNIT_COST AS SALESUNITPRICEBULK,

(ISU.QUANTITY * -1) * ISU.UNIT_COST AS TOTAL_NET_AMOUNT,

POVL.CURR_NET_CST / POVL.UOM_MULT AS PURCHASEUNITPRICEEA,

POVL.CURR_NET_CST AS PURCHASEUNITPRICEBULK,

POVL.CURR_NET_CST * (ISU.QUANTITY * -1) / POVL.UOM_MULT

AS EXTENDEDPURCHASECOST

FROM LAWSON_REPORT.PHSOR.ORG (P373711), P373711.REPORT.MM_ISSUES ISU,

IssueandTransfers.AutoRange_Sheet2 STF,

LAWSON_REPORT.PHSOR.ORG (P373711), P373711.MV.POVAGRMTLN POVL,

LAWSON_REPORT.PHSOR.ORG (P373711), P373711.MV.POAGMTPART POA

WHERE STF.COMPANY = ISU.COMPANY

AND STF.REQLOCATION = ISU.FROM_TO_LOC

AND POVL.VENDOR = ISU.VENDOR

AND POVL.ITEM = ISU.ITEM

AND POA.PROCURE_GROUP = POVL.PROCURE_GROUP

AND POA.VEN_AGRMT_REF) = POVL.VEN_AGRMT_REF

AND POVL.HOLD_FLAG = ‘N’

AND ISU.TRANS_DATE BETWEEN ‘01-AUG-14’ AND ‘31-AUG-14’

AND (POA.COMPANY = 5 OR POA.LOCATION = ‘PSPD’)

AND ISU.DOC_TYPE IN (‘IS’,‘IT’)

I’ve been tinkering for the past few days and I think I can get Toad to generate a message about any table not existing. it just needs to be the 1st table in the FROM Clause. I can get the query to execute if I put a (:wink: at the end of the last table, effectively stopping the code without reading the WHERE clause. If I try to execute it with the WHERE clause I get an error message saying that the MM_ISSUES table does not exist.

4

I notice that you are not using inner/outer join syntax. We usually use the ANSI join syntax. Try changing your query to that type. There is a button in the Query builder to use this syntax when building queries.

5

Try this for your From and Where clauses:

FROM LAWSON_REPORT.PHSOR.ORG (P373711), P373711.REPORT.MM_ISSUES ISU

JOIN IssueandTransfers.AutoRange_Sheet2 STF

ON STF.REQLOCATION = ISU.FROM_TO_LOC

AND STF.COMPANY = ISU.COMPANY

JOIN LAWSON_REPORT.PHSOR.ORG (P373711), P373711.MV.POVAGRMTLN POVL

ON POVL.VENDOR = ISU.VENDOR

AND POVL.ITEM = ISU.ITEM

JOIN LAWSON_REPORT.PHSOR.ORG (P373711), P373711.MV.POAGMTPART POA

ON POA.PROCURE_GROUP = POVL.PROCURE_GROUP

AND POA.VEN_AGRMT_REF) = POVL.VEN_AGRMT_REF

WHERE POVL.HOLD_FLAG = ‘N’

AND ISU.TRANS_DATE BETWEEN ‘01-AUG-14’ AND ‘31-AUG-14’

AND (POA.COMPANY = 5 OR POA.LOCATION = ‘PSPD’)

AND ISU.DOC_TYPE IN (‘IS’,‘IT’)

You can even move all the where conditions into your from clause and get rid of the where clause (may make it work faster).