Toad World® Forums

F9 fails trying to use a CTE-name as an independent table

All,

I’m still on Toad 12.11.0.22

while working on a script (anonymous block) I block the query used
to define a cursor, then hit F9, expecting to have the result grid populated.

Usually, this works, but sometimes I get an ORA-00942: table or view does not exist

Spooling the SQL shows that the failing statement is:
select *
from current_rows

Oracle is correct in flagging this as an error: current_rows is not the name
of any table; it is the name of a CTE defined in the cursor.

This is the cursor definition:
cursor load_messages
is
with current_rows
as (select pl.pono
, pl.porowpos
, pl.porowseq
, max(pl.rowstatus_new) keep (dense_rank last order by pl.timestamp) as rowstatus
, max(pl.timestamp) as timestamp
from iib_krg.porow_log pl
where pl.status not in (‘0’, ‘1’) – exclude unprocessed events
and pono = 2116943
group by pl.pono, pl.porowpos, pl.porowseq
having max(pl.change_type) keep (dense_rank last order by pl.timestamp) in (‘I’, ‘U’) – skip deleted rows
and max(pl.otype_new) keep (dense_rank last order by pl.timestamp) = ‘31’
and max(pl.rowstatus_new) keep (dense_rank last order by pl.timestamp) in (300, 310, 320, 322)
and max(pl.podeltype_new) keep (dense_rank last order by pl.timestamp) in (‘L’, ‘AT’))
, po_full
as (select cr.pono
, -1 as porowpos
, -1 as porowseq
, max(cr.timestamp) as timestamp
from current_rows cr
group by cr.pono)
, asn_c
as (select cr.pono
, cr.porowpos
, cr.porowseq
, cr.timestamp
from current_rows cr
where cr.rowstatus = 322)
select pono
, porowpos
, porowseq
, timestamp
from po_full
union all
select pono
, porowpos
, porowseq
, timestamp
from asn_c
order by pono
, porowpos
, porowseq
, timestamp;

Can you please fix this issue?

Thanks in advance & Kind regards,
Abe Kornelis

All,

when I execute the very same query after copying it into a separate editor tab, the problem is no longer there.

It seems the error only pops up when executing a CTE-prefixed query that is defined as a cursor…

Kind regards,
Abe