I have a query that was originally created as a cross-connection query due to the need to include data from an Excel file source. The Excel data source is no longer needed and the remaining data in the query all comes from a single Oracle database connection. Is there any straightforward way to convert the query from using a heterogeneous connection to just an Oracle connection?
Assuming that you’re working with a query builder file, probably easiest solution here is to simply open the QBF, yank out the Excel file reference from your query, add whichever Oracle table contains the info that used to come from Excel, re-run to test the query, and then re-save the QBF. I’ll defer to one of our Dev team members if, by chance, there might be a “back-end” way to change the connection profile.
I did that, but all indications are that it is still treated as a cross-connection query (with the additional overhead that entails) rather than a native Oracle connection. The query file is a .TSM extension if that makes a difference.
The difference on cross-queries is how we fully qualify the tables. With Cross-query we include the connection alias as the first part of the object name. Here is an example.
Cross-connection
Select * from MyOracleConnection.MySchema.Mytable
Regular query connected to oracle
Select * from MySchema.Mytable
So take out the connection part of the fully qualified object.
If I remove the connection alias there are two resulting errors:
- The Visualize Query function does not work to synchronize the SQL statement and the diagram.
- Ignoring step 1 and executing the query from the SQL statement results in the error ‘Connection not found “MySchema”’.
I know that I can copy the modified SQL to a new query builder that is not cross-connection, but I was trying to discover if there is a way to modify it in place and preserve the diagram. We have differing levels of users and some are more comfortable working with the diagram.
The Cross-connection Query Builder and regular builder do run differently. It knows how to change from one to the other when you drag on two disparate database tables on the diagram tab but not from the query tab or when you visualize.
You could try using the diagram tag and delete the excel tables. As long as you only have Oracle tables left it will now be a regular query builder again and change the SQL. You can now add back additional Oracle tables and move forward as a non-Cross-Query.
Thank you. I think my situation may have been complicated by having a union set. Simply removing the Excel table it still remained as a cross-connection query, but adding/removing Oracle tables to the other sets caused the query to revert to non-cross-connection.