Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Nidhika.

Asked: November 06, 2018 - 11:15 am UTC

Last updated: November 06, 2018 - 3:27 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi Tom

In my application uses SSRS tool and PLSQL .According the requirement I have category as a in parameter like 'AD','SA','SSV' and 'SOA' , so I have written single query with union all operator for all category. Now problem is my query has become very big and result of this query I am passing as ref cursor variable and this ref cursor variable result is accepted by ssrs dataset.
Is there any way, through that I can split query into multiple ref cursor variables of each categories and do the union all of all ref-cursor variable into single refcursor variable and pass result to ssrs report.

I am thinking to do pipeline function, but I am not sure whether it will work or not.
Please help me out to resolve this issue how can I split big query.

and Chris said...

Splitting SQL with unions alls is usually a matter of extracting each subquery into its own statement. You'll need to share your query for us to give more meaningful help.

But.

In general you don't want to.

If the subqueries are mutually exclusive and you're worried about performance, don't be. The database can optimize the query so it only runs the subqueries matching the input parameter:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9538822000346932676

If maintainability is your issue, there are a couple of techniques you can use to help. One is to use the with clause to encapsulate your subqueries:

with complex1 as (
  select big_query1 ...
), complex2 as (
  select big_query2 ...
), ...
  select * 
  from   complex1
  union all
  select * 
  from   complex2
  ...


Or you could stick the queries in "proper" views. And union-all those.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.