Solved

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

Posted on 2010-09-16
6
3,367 Views
Last Modified: 2012-05-10
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?
0
Comment
Question by:D-pk
  • 3
  • 2
6 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 33697734
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
 
LVL 23

Accepted Solution

by:
paquicuba earned 250 total points
ID: 33698249
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
 

Author Comment

by:D-pk
ID: 33703478
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:D-pk
ID: 33703801
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
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 250 total points
ID: 33706217
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
 

Author Comment

by:D-pk
ID: 33728815
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

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now