CODEWITHSUNDEEP
oracle-databaseoracle10g
user8512043
user8512043

Reputation: 1157

ORA-32031: illegal reference of a query name in WITH clause

I am trying to create CTE with Oracle 10g and unfortunately getting the below error

ORA-32031: illegal reference of a query name in WITH clause

Is there any way to resolve or alternate solution to make it work? (Unfortunately I've to use the old version)

Struggling with the exception and after few hours of trying as well researching, it seems to be a versioning issue that means the CTE isn't supported in the version I am working with. I am not sure but when I tried the query in SQL Server 2008, it worked just fine. This is the tried query so far:

with Q1 as
(
    select m.id, m.value, row_number() over (partition by id order by storedate) as n
    from sample_test_2020 m
),
Q2 as
(
    select Q1.id, value as s, n as m, lpad(n, 3, '0') as p 
    from Q1
    where value <= 8 
    union all
    select Q1.id, Q1.value + s as s, Q1.n as m, Q2.p || ',' || lpad(Q1.n, 3, '0') as p
    from Q1
    inner join Q2 on Q2.id = Q1.id and Q1.n > Q2.m and Q1.value + s <= 8
),
Q3 as
(
    select id, min(p) as p
    from Q2 
    where s = 8
    group by id
)

select * from Q3;

Expected output:

ID   P
1122 001,004
4466 001,004

Table:

CREATE TABLE SAMPLE_TEST_2020 
(
  ID VARCHAR2(20 BYTE) 
, STOREDATE DATE 
, VALUE NUMBER 
, INFO VARCHAR2(20 BYTE) 
)

Sample Input:

ID      STOREDATE   VALUE INFO
4466    01-JAN-20   2     DONE
4466    02-JAN-20   2     DONE
4466    03-JAN-20   2     DONE
4466    04-JAN-20   6     DONE
1122    01-JAN-20   2     DONE
1122    02-JAN-20   2     DONE
1122    03-JAN-20   2     DONE
1122    04-JAN-20   6     DONE

For the reference that worked in SQL Server, here is the working solution - Working Sample

Upvotes: 0

Views: 2725

Answers (2)

Ƭ̵̬̊
Ƭ̵̬̊

Reputation: 1

I had this same error code pop up in an OBIEE analytics platform. I had a, with block, being called successfully in another with block, before I did a refactor.

error appeared after the refactor. I had moved the nested with block further down in the code file.

The parser in my instance was reading from the top of the file down. by moving the, called "nested" with block, to be the first with block in the file... the error disappeared.

Upvotes: 0

Chris Saxon
Chris Saxon

Reputation: 9875

You need a more recent version of Oracle Database!

While you're still stuck on 10g, a connect by along these lines should work: 

with rws as (
  select m.id, m.value, 
         row_number() over (
           partition by id order by storedate
         ) as rn
  from sample_test_2020 m
), tree as (
  select id, lpad ( rn, 3, '0' ) rn, prior value + value v
  from   rws
  start with value <= 8
  connect by prior rn < rn
  and prior id = id
  and prior value + value <= 8
)
  select id, min ( rn ) || ',' || max ( rn ) p
  from   tree
  group  by id;

ID      P         
1122    001,004    
4466    001,004

Though to be honest I'm confused as to the purpose of your query. It looks like it calculates some kind of running total - it may be possible to do this using sum ... over ( ... )

Upvotes: 3

Related Questions