Delete with subquery which includes CTE's (Common Table Expressions)
Why does this fail a syntax check? Please don't worry about giving work-a-rounds, I can do that, the real question is why does Oracle not support CTE's for delete statement subqueries?
The Oracle docs don't mention this as a restriction? Or do they and I missed it?
Version : 10.2.0.4 (Windows)
delete crt_scheduled_task
where scheduled_task_id in (
with last_updated_dates as (
select st.scheduled_task_id, st.created, max(sst.updated) last_updated
from crt_scheduled_task st
left join crt_scheduled_sub_task sst
on st.scheduled_task_id = sst.scheduled_task_id
group by st.scheduled_task_id, st.created)
,last_touched_date as (
-- if sub tasks exist the max of updated for the
-- scheduled sub-tasks is the last_touched date else
-- the created date is the date
select scheduled_task_id
,case when last_updated is null
then created
else last_updated
end last_touched
from last_updated_dates)
select scheduled_task_id
from last_touched_date
where last_touched < p_cutoff_date)
and scheduled_task_status_id <> 1 /*in progress*/;