I ran into the above mentioned error.
Here's my query:
WITH periods(Thanks Tom!) Pretty simple right? That's what I thought.
SELECT datefrom, datethru
WHERE periodtypeid = 'WEEK'
AND TRUNC( SYSDATE - datefrom ) BETWEEN 0 AND 56
FROM vw_periodsHmmm...what's that? Since I can't get to the docs right now, I'll point you here.
ERROR at line 6:
ORA-32031: illegal reference of a query name in WITH clause
Cause:That's not very helpful.
forward or recursive reference of a query name in WITH clause is not allowed.
Correct query statement, then retry.
What's the definition of the view?
CREATE OR REPLACENothing there...wait, what's the name of the table? Periods? Couldn't be that simple (and yet so obvious) could it?
SQL>WITH perOf course it can!
4 SELECT datefrom, datethru
5 FROM vw_periods
6 WHERE periodtypeid = 'WEEK'
7 AND TRUNC( SYSDATE - datefrom ) BETWEEN 0 AND 56
9 SELECT *
10 FROM per;
8 rows selected.
Lesson? Don't try to name your subquery with the same name as the base table.