Proper use of subquery factoring (WITH clause)

From: <dschoch_at_yahoo.com>
Date: 7 Apr 2005 09:11:18 -0700
Message-ID: <1112890278.373897.325270@f14g2000cwb.googlegroups.com>

I'm on Oracle 9.2.0.3, using SQL*Plus 9.2.0.1.0 and I have an existing report that is comprised of a 12-legged union, each leg selecting from a relatively slow view. The run time of the report is roughly 12 times a "select count(*)" from the view and is way too long. I would like to use Oracle's "subquery factoring clause" - better known as WITH - in this query, but I can't get it to run.

Using Oracle's sample EMPLOYEES and DEPARTMENTS tables to illustrate what I'm attempting, I created the following view:

create or replace view v_empdept as (

    select e.employee_id       as empid,
           e.first_name        as fname,
           e.last_name         as lname,
           e.department_id     as deptid,
           d.department_name   as deptname

    from employees e
    join departments d on d.department_id = e.department_id)

With this view, the following query was written. (Please note that there is no point to this query other than to illustrate the problem.)

WITH SUMRY AS (
    SELECT empid as sumry_empid,

            deptname as sumry_deptname
    FROM v_empdept
    WHERE deptid >= 30 )
SELECT 'A' as L,

       sumry_deptname as DEPT,
       count(distinct sumry_empid) as CNT

FROM sumry
WHERE sumry_deptname like '%a%'
GROUP BY sumry_deptname
UNION ALL
SELECT 'I' as L,

       sumry_deptname as DEPT,
       count(distinct sumry_empid) as CNT

FROM sumry
WHERE sumry_deptname like '%i%'
GROUP BY sumry_deptname

I receive the following error:

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.

When I write a similar query, but reference the tables in the WITH clause instead of the view, it also refuses to run (I should point out that I have had some success doing a similar query against my real-live tables instead of the view, but not with these sample tables - ??). This is very similar in structure to what's in the Oracle manual under the Subquery Factoring: Example for the SELECT statement:

WITH SUMRY AS (
    SELECT e.employee_id as sumry_empid,

            d.department_name as sumry_deptname     FROM employees e
    JOIN departments d on d.department_id = e.department_id     WHERE e.department_id >= 30 )
SELECT 'A' as L,

       sumry_deptname as DEPT,
       count(distinct sumry_empid) as CNT

FROM sumry
WHERE sumry_deptname like '%a%'
GROUP BY sumry_deptname
UNION ALL
SELECT 'I' as L,

       sumry_deptname as DEPT,
       count(distinct sumry_empid) as CNT

FROM sumry
WHERE sumry_deptname like '%i%'
GROUP BY sumry_deptname

Yields the message:

WITH SUMRY AS (
*

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1 ORA-00904: "from$_subquery$_003"."QCSJ_C003000_10": invalid identifier

Again, if I eliminate the UNION ALL and everything following, it runs.

I'm obviously doing something wrong and have poured over the Oracle documentation, but can't figure out what I'm missing. Any help is appreciated.

-Dan- Received on Thu Apr 07 2005 - 11:11:18 CDT