Dependent WITH clauses in Oracle Reports

Why does Oracle's Report builder(connected to 11g DB) not allowing the following valid SQL?

with abc as 
(Select 1 as x from dual),
def as
(Select 2 as y from abc)
select x,y from abc,def

I am getting ORA-00942: table or view does not exist (Select 2 as y from ==>abc) for the above. When I execute the above SQL from SQL Developer connected to the same 11g database, it runs without any problems. The following independent WITH clauses is a开发者_运维技巧ccepted by the reports builder though,

with abc as 
(Select 1 as x from dual),
def as
(Select 2 as y from dual)
select x,y from abc,def

Is there a way to do this in reports builder or should I just have to cram the first WITH clause in the second one's FROM clause?. Thanks in advance.

PS:Report Builder version is 10.1.2.0.2

---

There is a workaround for WITH clause problems posted on the Oracle forums here: https://forums.oracle.com/forums/thread.jspa?threadID=710036

That you could implement to get around your problem.

Alternatively, could you not just use the current WITH queries within inline views in your query?

EDIT: Like this (using your code).

SELECT x, y
  FROM 
       (SELECT 1 AS x
          FROM dual) abc,
       (SELECT 2 AS y
          FROM dual) def

---

I have had this same issue and this is how I overcame it: Where you are using the inline alias, explicitly join it (inner, outer...) to another table. example:

with MyTabA as(
    select 1 theID, 'AA'
    thetext from dual
),
MytabB as(
    select 1 theID, '123 street Paul'
    theaddress from dual union select 2 theID, '123 street Paul'
    theaddress from dual union select 3 theID, '123 street Paul'
    theaddress from dual
)
Select MytabA.theid, mytaba.thetext, mytabb.theaddress
from MyTabA, MyTabB
Where MyTabA.theID = MyTabB.theid
;