How to reuse a subquery

oracle, oracle11g, subquery

Solution

Subquery factoring (aka CTEs in other database platforms) is what you need, eg:

with dataset as (select datasetid
                 from   Reportingdatasetmembers
                 where  ReportingDatasetID = param_in_ReportingDataSetID)
select ...
from   some_table_1
where  ...
and    datasetid in (select datasetid from dataset)
union all
select ...
from   some_table_2
where  ...
and    datasetid in (select datasetid from dataset);

Problem

I have a long stored procedure. Many times in the stored proc, the subquery below (in parenthesis) is repeated. ``` and datasetid IN (select datasetid from Reportingdatasetmembers where ReportingDatasetID = param_in_ReportingDataSetID) ``` Can I consolidate that code since it is repeated? I.e., in SQL Server, I would declare a table variable. Then insert the rows into the table variable. Then query against the table variable. In the least, this helps apply the DRY principle. Is there an equivalent way to consolidate this in Oracle? Oracle table collections don't seem to be the reduce the code base. I believe that CTEs are out of the question because they cannot be reused?

Original source