Toad Data Point 5.0.6.97 (64 bit).
So here is a query that runs with a single connection:
with
contract_by_TIN as
(
select provid, name_full as fullname
from semantic_db.provider
where part_state = 'WA' and provider_tax_id in ('911020139','911936999')
)
select ms.year_mo, count(ms.pat_id) as mm
from semantic_db.medinsight_membermonths as ms
inner join
contract_by_TIN as am on am.provid = ms.prov_pcp_ipa
where part_state = 'WA'
group by year_mo
order by year_mo
Here is the same query, pasted into the cross connection query editor:
with
contract_by_TIN as
(
select provid, name_full as fullname
from Impala_PROD, semantic_db.semantic_db.provider
where part_state = 'WA' and provider_tax_id in ('911020139','911936999')
)
select ms.year_mo, count(ms.pat_id) as mm
from Impala_PROD, semantic_db.semantic_db.medinsight_membermonths as ms
inner join
contract_by_TIN as am on am.provid = ms.prov_pcp_ipa
where part_state = 'WA'
group by year_mo
order by year_mo
It highlights the contract_by_TIN as syntax error. When I hit F5 the output is
ERROR: line 11, column 2, ending line 11, column 16: Found 'contract_by_TIN': Not a Heterogeneous Query extension
When I hit F9 the output is the same as for F5.
If I comment out the CTE and the join on the CTE of course it runs, but that's not what I needed it to do lol.