ORA-32031: illegal reference of a query name in WITH clause I was trying to use the subquery factoring clause, WITH, in a query as it was a fairly small subset but called a number of times...with the same predicates.

I ran into the above mentioned error.

Here's my query:

WITH periods
AS
(
  SELECT datefrom, datethru
  FROM vw_periods
  WHERE periodtypeid = 'WEEK'
    AND TRUNC( SYSDATE - datefrom ) BETWEEN 0 AND 56
)
SELECT *
FROM periods;

(Thanks Tom!) Pretty simple right? That's what I thought.

  FROM vw_periods
       *
ERROR at line 6:
ORA-32031: illegal reference of a query name in WITH clause

Hmmm...what's that? Since I can't get to the docs right now, I'll point you here.

Cause:
forward or recursive reference of a query name in WITH clause is not allowed.
Action:
Correct query statement, then retry.

That's not very helpful.

What's the definition of the view?

CREATE OR REPLACE
VIEW vw_periods
AS
SELECT
  periodid,
  periodtypeid,
  datefrom,
  datethru
FROM periods;

Nothing there...wait, what's the name of the table? Periods? Couldn't be that simple (and yet so obvious) could it?

SQL>WITH per
  2  AS
  3  (
  4    SELECT datefrom, datethru
  5    FROM vw_periods
  6    WHERE periodtypeid = 'WEEK'
  7      AND TRUNC( SYSDATE - datefrom ) BETWEEN 0 AND 56
  8  )
  9  SELECT *
 10  FROM per;

DATEFROM  DATETHRU
--------- ---------
08-DEC-08 14-DEC-08
15-DEC-08 21-DEC-08
22-DEC-08 28-DEC-08
29-DEC-08 04-JAN-09
05-JAN-09 11-JAN-09
12-JAN-09 18-JAN-09
19-JAN-09 25-JAN-09
26-JAN-09 01-FEB-09

8 rows selected.

Of course it can!

Lesson? Don't try to name your subquery with the same name as the base table.

Labels: error, oracle, sql

  ¶ 1/29/2009 11:28:00 PM