Solved

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

Posted on 2010-09-16
6
3,334 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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 to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
oracle rollup query 3 37
Oracle 12c patching 1 37
report returning null 21 52
ORA-12560: TNS:protocol adapter error 8 49
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.‚Äč
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

757 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

20 Experts available now in Live!

Get 1:1 Help Now