Thursday, January 29, 2009

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.

2 comments:

Anonymous said...

Yuck. Even worse, the query names in a with clause cause name capture inside of views. Just name a query the same as a table used in a view and then use that view in another another named query. The view will actually hit your named query instead of the table.

Bonus lunacy points: "select * from view" can now fail with a column not found error.

Jacob said...

Ah, this is a simple fix, but helped me get through a similar error. Thanks for the post.