Solved

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

Posted on 2010-09-16
6
3,459 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

How our DevOps Teams Maximize Uptime

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

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Check ALL SP in database make sure there are no errors 17 61
Formating field in mysql Advance formatting 1 41
pl/sql parameter is null sometimes 2 25
SQL Query help 3 24
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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 syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

733 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