“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