CODEWITHSUNDEEP
oracle-databaseperformanceplsqlquery-optimization
David B
David B

Reputation: 3571

How to store a query into a variable

Let's say I have this complex query snippet

select id, name, count(*)
from items
join ...
where <where_1st>
group by ...
having <having_1st>

From the table definition of items

items
-----
id ID
name varchar2
quantity number
price number
...

How can I store that query to a variable such that I can use a field from that query and at the same time join that query in a union all format. Something like this.

Final Query:

select id, name, count(*)
from items
join ...
where <where_2nd> and id not in (<first_query>.id/s)
group by ...
having <having_2nd>

union all

<first query>;

Can it be done in this format?

DECLARE
  <variable_for_1st_query>
BEGIN
  <final_query>
END;

Note: The query is extensive and complex. Taking this approach to reduce computing time by half.

Upvotes: 1

Views: 174

Answers (3)

Frank Ockenfuss
Frank Ockenfuss

Reputation: 2043

A assume you want to use the results of your first query twice:

  • for your selection itself
  • for excluding the same ids in your second selection

If your first and second select delivers the same result for count(*) then you can simply use union instead of union all and remove the and id not in (<first_query>.id/s) clause in your second select statement.

e.g.

-- test data for "items"
with items(id, name, val) as
 (select 1, '101', 101 from dual
  union all
  select 1, '101', 501 from dual
  union all
  select 2, '202', 202 from dual
  union all
  select 2, '202', 302 from dual
  union all
  select 3, '301', 103 from dual
  union all
  select 5, '105', 105 from dual)

select i.id, i.name, count(*)
  from items i
 where i.id < 5 -- different select ranges
 group by i.id, i.name
having avg (val) between 200 and 505 -- different group criterias

union 

select i.id, i.name, count(*)
  from items i
 where i.id > 1 --
 group by i.id, i.name
having sum (val) <= 505; --

--> result:

1   101 2
2   202 2  -- result is in both selects
3   301 1
5   105 1

If your first and second select delivers different result for count(*) then you can achive it by 

-- select all
with data as (
select 1 as select_id, id, name, count(*) as total
from items
join ...
where <where_first> 
group by ...
having <having_first>
union all
select 2 as select_id, id, name, count(*) as total
from items
join ...
where <where_2nd> 
group by ...
having <having_2nd>)
-- filter results by select_id
select d.id, d.name, d.total from data d
where d.select_id = (select min(dd.select_id) from data dd where dd.id=d.id)

Upvotes: 1

APC
APC

Reputation: 146239

What you want to do is re-use the result set of the first query as a filter to the second query while also including its rows in the final output. There are a couple of ways of doing this, but the simplest - no PL/SQL required - is subquery factoring AKA the with clause. The with statement is executed once and the result set is used wherever the subquery is referenced.

So, in your example:

with q1 as (
    select id, name, count(*)
    from items
    join ...
    where <where_1st>
    group by ...
    having <having_1st> 
)
select id, name, count(*)
from items
join ...
where <where_2nd> 
and id not in (select q1.id from q1)
group by ...
having <having_2nd>
union all
select * from q1;

Because this approach is pure SQL it is easy to read the resultset. A PL/SQL approach would require a lot more infrastructure.

Upvotes: 2

Sindhu
Sindhu

Reputation: 418

DECLARE 

v_query_1       VARCHAR2(100);
v_query_2       VARCHAR2(200);
v_query_3       VARCHAR2(300);
v_query_main    VARCHAR2(500);

BEGIN

v_query_1    := 'First query';
v_query_2    := 'Second query';
v_query_3    := 'Third query';
v_query_main := v_query_1||'UNION ALL'||v_query_2||'UNION ALL'||v_query_3;

EXECUTE IMMEDIATE v_query_main;

END;
/

Upvotes: 0

Related Questions