Forgive me if this has been asked, I didn't find it in the forums.
When I write a cross connection query with a CTE, it either says the CTE alias is a syntax error or it says the table does not exist. My CTEs so far always involve a single connection. Sometimes I use a CTE several times in a query and often the CTEs are involved so just writing the subquery over and over would really degrade readability, but I know I COULD do that if I needed to.
Am I missing something obvious about how to alias CTEs, or is it silly to even expect that CTEs would work across connections?
What version of Toad are you on? Is Toad reporting a syntax error in the Editor or are you getting an ORA-nnnnn exception upon execution? How are you executing... F9 (execute statement at caret), F5 (execute as script), or Ctrl+Enter/Shift+F9? Posting a sample CTE that's failing would help as well.
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.
This is the Toad for Oracle forum. The Toad Data Point forum can be found here... https://forums.toadworld.com/c/toad-data-point/35. You will need to post your question to that forum to reach the correct audience.
Sorry about that, I will put it in the right place...