Hi,
I am trying to run a query with a few “connect by” sub queries and a CTE, and it runs just fine. But when I try join with excel to filter some records based on columns in the excel file I get all kinds of errors related to incorrect syntax.
Does it have smth do to with translation of oracle syntax to access syntax?
The joins with between standalone excel and oracle tables also works fine.
Hi,
"a query of join datasource_A_table with datasource_B_table" - we usually call it cross query.
the mechanism of cross query is a little complicated, I'd like to explain it by pictures:
Below is a typical cross query:
the data of each sub-query would be fetched separately, and then do "JOIN operation" in TIC server.
Since TIC server is actually a mysql Server, the top-level query who does the join would be executed in mysql, which means the top-level query should use MySQL grammar, if it has function in selected columns, the function must be MYSQL functions. Here we have: Rule 1: The top-level query of a cross query should use MySQL grammar
in the case above, the sub-query is just a table name, it's the simplest case. Sometimes the sub-query can be a real query, e.g.
if the sub query is an odbc driver datasource, in the process of fetching data, the whole sub-query would be pushed-down to remote datasource and executed there. If the sub-query is of oracle datasource, you can use oracle functions in selected columns in sub-query.
Here we have Rule 2: you can use the target datasource's grammar in sub-query if the sub-query can be pushed-down (in most of the cases, they are odbc driver datasources)
I would like to give you an example of a common issue , e.g.
this query would get failed because it uses oracle function on top-level query, how do we solve it?
Answer: we should move the "to_char” function to sub-query, and the whole sub-query would be pushed down to remote oracle database.
Do I resolve your confussion? If you still feel puzzled, please post your example, I would be very glad to discuss with you
This blog might help also. https://blog.toadworld.com/2012/11/21/how-to-write-a-cross-database-query
