Toad can't find CTE table in cross connection editor but runs in single query connection?

Toad Data Point
1

When I run the following :

with CTE_A as (
select * from Teradata_ODBC.[schema].[table]
)
select * from CTE_A ;

in a single connection query, it runs successfully. However, in a cross connection editor, I get the following error: ERROR: line 4, column 17, ending line 4, column 21: Found 'CTE_A': Not a Heterogeneous Query extension.

I am attempting to join two tables together from different databases. Is this possible?

2

Couple of questions...

  • Have you installed a supported ODBC driver for Teradata? The Cross-Connect engine of both the Editor and Query Builder in these modes use ODBC for blending the data from various platforms.

  • Did you create your cross-connect query using the Query Builder?
    Don't know about you, but if I tried to remember the syntax for mixing and matching different tables from different platforms, there'd be a 99% chance that I'd introduce a typo, or get the syntax wrong.

  • Just guessing here, but CTEs could be considered as multi-statement request, which TDP does not (yet) support (see the TDP Help file for limitations, etc.)

3

Hi Gary!

  1. Yes, I am able to union all the two tables together (although anything like a join does not work, unsure if related). I am also able to query them separately in the cross connect editor.
  2. To my knowledge, I think the syntax might be the same between the two platforms as I am just doing a select * from one table
  3. Got it! That makes sense - when I try to join them together (not in a CTE) i get the following: The remote data object does not exist: "[schema_name]"."[table_name]"; however, again, I am able to union all the tables together

Any help would be appreciated!