Home Ask Login Register

Developers Planet

Your answer is one click away!

ziggy February 2016

Is it possible to use the Oracle sql WITH CLAUSE on multiple unrelated queries within the same file?

I have an SQL script that generates a report through SQL plus. The report is generated by running multiple unrelated queries one after the other. Some of the queries have common code that i would like to try and only define once. As an example, assume the report generation file contains the following two scripts

Select a, b, c
from x
where a in (
    select x
    From d where x like '%ABC%'
)
/

Select a, d, y
from z
where a in (
    select x
    From d where x like '%ABC%'
)
/

The two queries are completely different but both use the same subquery. I would like to avoid having to rewrite the subquery for each independent query.

The WITH clause can be used but i am not sure how to use it on two different queries

with t1 as (
 select x
 from d where x like '%ABC%'
)
Select a, b, c
from x, t1
where t1.x = x.a

The above resolves it for the first query. How can i use the same WITH statement for the second query? or is there an alternative approach to achieve this?

Edit

One other option is to use refcursors. This might work but i might be very tedious to build the strings for each query and concatenate it with a generic string query. As it is a plsql block, it will also be difficult to generate headers between the different queries.

Declare
    v_sql varchar(2000);
Begin
    v_sql := 'Select 1 from dual';
    dbms_output.put_line(v_sql);
    Open :x for v_sql;
End;
/
print x;

Thanks

Answers


Gordon Linoff February 2016

You cannot use a common table expression (CTE) for multiple queries. This is functionality for a single query.

I would recommend a view or a temporary table.

Post Status

Asked in February 2016
Viewed 2,045 times
Voted 11
Answered 1 times

Search




Leave an answer


Quote of the day: live life