Toad World® Forums

CTEs in cross connection queries

#1

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?

#2

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.

#3

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.

#4

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.

#5

Sorry about that, I will put it in the right place...