?
Solved

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

Posted on 2010-09-16
6
Medium Priority
?
3,576 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1000 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
Quick Start: DOCKER

Sometimes you just need a Quick Start on a topic in order to begin using it.. this is just what you need to know to get up and running with Docker!

 

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 1000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

770 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