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
;
精彩评论