Oracle Database Discussions

Hi,

When I try to run the below code, I receive the following error:

ORA-32036: unsupported case for inlining of query name in WITH clause

From searching around, it appears that this means that one/some of the named queries are being inlined multiple times into the query (SELECT * FROM (SELECT *....) which is unsupported. The query runs fine when I am not attempting to create the materialized view, so I'm happy with the SQL and I'm just trying to create the view.

1. Does anyone know how I can find out which of the named subqueries are causing the problem?
2. How can I prevent this?
3. If I can't prevent this, do I have to do the inlining manually? This would be very annoying because I'll have to repeat a load of where clauses.

I'm running 11gR2.

Thanks for the help,

Dan Scott

CREATE TABLE TEST1 (
    "PERSON_ID" NUMBER NOT NULL ENABLE,
    "STAY_ID"   NUMBER(7,0) NOT NULL ENABLE,
    "STAY_INTIME" DATE NOT NULL ENABLE,
    "STAY_OUTTIME" DATE NOT NULL ENABLE
);

CREATE MATERIALIZED VIEW test_mview AS
With
/* Get all stays matching a particular condition (WHERE clause removed for clarity) */
stays as (
  SELECT
    person_id,
    stay_id,
    stay_intime,
    stay_outtime
  FROM test1
),
-- Create daily periods for each stay
stay_days as (
SELECT
  person_id,
  stay_id,
  stay_intime stay_day_intime,
  stay_outtime stay_day_outtime,
  rn stay_day
FROM stays
  MODEL PARTITION BY (person_id,stay_id)
  DIMENSION BY (1 rn)
  MEASURES (
    trunc(stay_outtime-stay_intime)+1 diff,
    stay_intime,
    stay_outtime,
    stay_outtime outtimeint
    )
  ( stay_intime[ for rn from 1 to diff[1] increment 1]=stay_intime[1] + numtodsinterval(cv(rn)-1, 'Day'),
    stay_outtime[ for rn from 1 to diff[1] increment 1]=case when cv(rn)=diff[1] then outtimeint[1]
                                                    else  stay_intime[1] + numtodsinterval(cv(rn), 'Day') end  )
),
/* Get a particular parameter within the daily period (This could be complicated, like summing/counting hourly measurements etc.) */
stay_param1 as (
  select i.person_id, i.stay_id, i.stay_day
    from stay_days i
),
/* Get a particular parameter within the daily period (This could be complicated, like summing/counting hourly measurements etc.). */
stay_param2 as (
  select i.person_id, i.stay_id, i.stay_day
    from stay_days i
),
/* Combine the parameters together (match each s1 with closest previous s2, etc. */
stay_param_comb as (
  select s1.stay_id, s1.person_id, s1.stay_day
    from stay_param1 s1,
         stay_param2 s2
   where s1.stay_id = s2.stay_id
)
select * from stay_param_comb;