Skip to Main Content

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

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.

We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.