I am trying to query cross instances using the query modeler, and I am getting this error with little help as to how to resolve.
"Column X is not prefixed with an alias which is required for a heterogeneous query"
Could it be that you are referencing two or more columns with the same name? Maybe the join column or columns? If so, try adding a field alias to one of the columns
That might have been it. However, adding the alias with an "as X" doesn't work, it has to be added as you show in the diagram area. many times I made an alteration that I could not recover from, undo did not work and had to close without saving an re-open. I have my query working, but if I add a "AS XYZ" in the query pane it starts giving this error and does not stop unless I close without saving and reopen.
I have a working Cross-Connection query that has an alias for every column in it, and when I add a "Where Table1.ColumnX <> Table2.ColumnY" line, it starts warning me this way again. I'm stuck. Even after removing the where clause, the error still remains.
I think it comes down to Toad not handling manual entry of a global where clause when using the Cross-Connection Query Mode. Using the Diagram seems to allow you to add the same global where clause.