ORA 32036 Unsupported case for inlining of query name in WITH clause error

I have a big SQL with many WITH clauses (materialized views), this was running fine for a long time. (it was running faster too) But now I get this error. Anyone have faced this error before. Thoughts?
D-pkAsked:
Who is Participating?
 
paquicubaConnect With a Mentor Commented:
I've seen it before when using ODBC (it's a bug to me)

You're referencing a with clause in another with clause and then using both with cluases in another one or in the main select statement.

For example:

with
tmp1 as(select dummy from dual),
tmp2 as(select dummy from tmp1 where dummy = 'X')
Select x.dummy from tmp1 x, tmp2 y
where x.dummy = y.dummy
/

Try renaming the with clause:

with
 tmp1_a as(select dummy from dual),
tmp1_b as(select dummy from tmp1_a),
 tmp2_a as(select dummy from tmp1_b where dummy = 'X')
 Select x.dummy from tmp1_a x, tmp2_a y
 where x.dummy = y.dummy
 /
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Can you give the complete query here so that someone can help you to get that fixed ?

also give the exact sql error message ( just copy it and paste it here ) ?

by any chance, this big sql was modified recently after which it is giving this error ?
0
 
D-pkAuthor Commented:
This is how my SQL looks like.
The exact error msg is:
"ORA-32036: unsupported case for inlining of query name in WITH clause"
WITH main_vw AS (SELECT /*+ materialize */ 
                       
                 ),
 
--Line 1 Total Eligible for HealthTrack
line_1 as (
SELECT
       'Line 1' Line,
       mv.Age,
       COUNT (distinct id) VALUE -- should be count on unique member ids
 
FROM   main_vw mv
GROUP BY  mv.Age
),
-----------------------------------------------------------------------------------------------------------------------------
line_2a as

(
  select  distinct 'Line 2a' Line, age, 
         (case when AGE = '<1' then 6
              when AGE = '1-2' then 4
              when AGE = '3-5' then 3
              when AGE = '6-9' then 2.5
              when AGE = '10-14' then 4.5
              when AGE = '15-18' then 4
              when AGE = '19-20' then 2
              END) Value
  from main_vw
),

line_2b as

(
  select  distinct 'Line 2b' Line, age, 
         (case when AGE = '<1' then 1
              when AGE = '1-2' then 2
              when AGE = '3-5' then 3
              when AGE = '6-9' then 4
              when AGE = '10-14' then 5
              when AGE = '15-18' then 4
              when AGE = '19-20' then 2
              END) Value
  from main_vw
), 

line_2c as

(
  select distinct 'Line 2c' Line, age, 
         (case when AGE = '<1' then 3
              when AGE = '1-2' then 1
              when AGE = '3-5' then 0.5
              when AGE = '6-9' then 0.3125
              when AGE = '10-14' then 0.45
              when AGE = '15-18' then 0.5
              when AGE = '19-20' then 0.5
               END) Value
  from main_vw
),
------------------------------------------------------------------------------------------------
 
Line_3a as (
 
SELECT
       -- this select uses only "main_vw" materialized view at the top

), 
------------------------------------------------------------------------------------------------

line_3b as 
(
 select 'Line 3b' Line,
         Line_1.age, (line_3a.value/line_1.value) /12 Value 
         from line_1, line_3a
  where
  line_1.age=line_3a.age
),

line_4 as

(
 select 'Line 4' Line,
         Line_2c.age, (line_3b1.value * line_2c.value) * 2 Value 
         from line_2c, line_3b1
  where
  line_2c.age=line_3b1.age      
), 
------------******************************-----------------------
line_5 as

(
 select 'Line 5' Line,
         Line_1.age, (line_4.value * line_1.value) Value 
         from line_1, line_4
  where
  line_1.age=line_4.age         
),

line_6_9 as (
select ....
-- This select uses only "main_vw" materialized view
),

line_7 as

(
 select 'Line 7' Line,
         Line_5.age, 
         (case when line_6_9.line = 'Line 6' then (line_6_9.value / line_5.value) end)Value 
         from line_5, line_6_9
  where
  line_5.age=line_6_9.age      
  and  line_6_9.line = 'Line 6'
),

line_8 as

(
 select 'Line 8' Line,
         Line_1.age, 
         (case when line_4.value > 1 then (line_1.value)
                                when line_4.value <= 1 then (Line_4.value * line_1.value)
                             end) Value 
  from line_1, line_4
  where
  line_1.age=line_4.age         
),

line_10 as

(
 select 'Line 10' Line,
         Line_8.age, 
         (case when line_6_9.line = 'Line 9' then (line_6_9.value / line_8.value) end) Value 
         from line_8, line_6_9
  where
  line_8.age=line_6_9.age
  and  line_6_9.line = 'Line 9'       
)

 select * from line_1
 
 union all
 
 select * from line_2a

 union all
 
 select * from line_2b
 
  union all
 
 select * from line_2c
 
union all

 select * from line_3a

union all

 select * from line_3b 

union all

 select * from line_4
 
union all

 select * from line_5

union all 

 select * from line_6_9
 
union all
 
 select * from line_7
 
union all
 
 select * from line_8
 
union all
 
 select * from line_10

Open in new window

0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
D-pkAuthor Commented:
I tried to run it step by step by uncommenting one by one, when I include the "line_3b" materialized view I got this error... Thanks
0
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
I tried to simulate your query with the below and it works perfectly for me in 10.2.0.4 oracle database.

Can you give your complete query without changing anything and also the exact error message which you get with the error code ?

WITH main_vw AS (select sysdate s1 , to_char(sysdate,'dd') age, rownum id , rownum value, 'rownum' line
from dual        ),
line_1 as (
SELECT
       'Line 1' Line,
       mv.Age,
       COUNT (distinct id) VALUE -- should be count on unique member ids
FROM   main_vw mv
GROUP BY  mv.Age
),
-----------------------------------------------------------------------------------------------------------------------------
line_2a as
(
  select  distinct 'Line 2a' Line, age,
         (case when AGE = '<1' then 6
              when AGE = '1-2' then 4
              when AGE = '3-5' then 3
              when AGE = '6-9' then 2.5
              when AGE = '10-14' then 4.5
              when AGE = '15-18' then 4
              when AGE = '19-20' then 2
              END) Value
  from main_vw
),
line_2b as
(
  select  distinct 'Line 2b' Line, age,
         (case when AGE = '<1' then 1
              when AGE = '1-2' then 2
              when AGE = '3-5' then 3
              when AGE = '6-9' then 4
              when AGE = '10-14' then 5
              when AGE = '15-18' then 4
              when AGE = '19-20' then 2
              END) Value
  from main_vw
),
line_2c as
(
  select distinct 'Line 2c' Line, age,
         (case when AGE = '<1' then 3
              when AGE = '1-2' then 1
              when AGE = '3-5' then 0.5
              when AGE = '6-9' then 0.3125
              when AGE = '10-14' then 0.45
              when AGE = '15-18' then 0.5
              when AGE = '19-20' then 0.5
               END) Value
  from main_vw
),
Line_3a as (
SELECT * from main_vw
),
line_3b as
(
 select 'Line 3b' Line,
         Line_1.age, (line_3a.value/line_1.value) /12 Value
         from line_1, line_3a
  where
  line_1.age=line_3a.age
),
line_4 as
(
 select 'Line 4' Line,
         Line_2c.age, (line_3b.value * line_2c.value) * 2 Value
         from line_2c, line_3b
  where
  line_2c.age=line_3b.age      
),
line_5 as
(
 select 'Line 5' Line,
         Line_1.age, (line_4.value * line_1.value) Value
         from line_1, line_4
  where
  line_1.age=line_4.age        
),
line_6_9 as (
select * from main_vw
),
line_7 as
(
 select 'Line 7' Line,
         Line_5.age,
         (case when line_6_9.line = 'Line 6' then (line_6_9.value / line_5.value) end)Value
         from line_5, line_6_9
  where
  line_5.age=line_6_9.age      
  and  line_6_9.line = 'Line 6'
),
line_8 as
(
 select 'Line 8' Line,
         Line_1.age,
         (case when line_4.value > 1 then (line_1.value)
                                when line_4.value <= 1 then (Line_4.value * line_1.value)
                             end) Value
  from line_1, line_4
  where
  line_1.age=line_4.age        
),
line_10 as
(
 select 'Line 10' Line,
         Line_8.age,
         (case when line_6_9.line = 'Line 9' then (line_6_9.value / line_8.value) end) Value
         from line_8, line_6_9
  where
  line_8.age=line_6_9.age
  and  line_6_9.line = 'Line 9'      
)
 select line,age,value from line_1
 union all
 select line,age,value from line_2a
 union all
 select line,age,value from line_2b
  union all
 select line,age,value from line_2c
union all
 select line,age,value from line_3a
union all
 select line,age,value from line_3b
union all
 select line,age,value from line_4
union all
 select line,age,value from line_5
union all
 select line,age,value from line_6_9
union all
 select line,age,value from line_7
union all
 select line,age,value from line_8
union all
 select line,age,value from line_10;
0
 
D-pkAuthor Commented:
is this is a new change in EE? whenever I try to close a question it isnt letting me close it right away, it waits for a few days for someone to object the points awarded.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.