Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Proper use of subquery factoring (WITH clause)

Re: Proper use of subquery factoring (WITH clause)

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Thu, 07 Apr 2005 19:51:21 +0100
Message-ID: <nqva511jkc2dq2mp1dvitalnjh3vvq5sqn@4ax.com>


On 7 Apr 2005 09:11:18 -0700, dschoch_at_yahoo.com wrote:

>I'm on Oracle 9.2.0.3

...
>WITH SUMRY AS (
>*
>ERROR at line 1:
>ORA-00604: error occurred at recursive SQL level 1
>ORA-00904: "from$_subquery$_006"."EMPLOYEE_ID_0": invalid identifier
>
>If I eliminate the UNION ALL and everything following, the query runs
>fine with just one SELECT. But as soon as the union and second select
>is added, it won't run.

 Sounds familiar. I also got various similar "invalid identifier" errors from recursive SQL in earlier patchsets of 9.2 with some combinations of WITH clauses, UNION ALLs and/or lots of ANSI-style joins (full outer joins were particularly buggy), each "solved" by rewriting the query to another equivalent form that happened to do things in a different order.

 They got less common with later patchsets. In particular 9.2.0.5 seemed to knock most occurrances on the head. Bug 3008893 seems a prime candidate.

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
Received on Thu Apr 07 2005 - 13:51:21 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US