How to Reuse a Large Query Without Repeating It

How to reuse a large query without repeating it?

create view horrible_query_1_VIEW as 
 select .. ...
  from .. .. ..

create view ugly_query_2_VIEW as 
 select .. ...
  from .. .. ..

Then

(horrible_query_1_VIEW) minus (ugly_query_2_VIEW)

(ugly_query_2_VIEW) minus (horrible_query_1_VIEW)

Or, maybe, with a with clause:

with horrible_query_1 as (
  select .. .. ..
    from .. .. ..
) ,
ugly_query_2 as (
  select .. .. ..
     .. .. ..
)
(select * from horrible_query_1 minus select * from ugly_query_2    ) union all
(select * from ugly_query_2     minus select * from horrible_query_1)

Reuse of select query result oracle

Use the first query within your subsequent queries. IE:

DELETE FROM ANOTHER_TABLE_1 WHERE ID IN (
     SELECT ID FROM MARMELADES mrm 
     where not exists 
         (SELECT 1 FROM TOYS toys 
         WHERE mrm.ID = toys.ID 
         AND mrm.INGREDIENT = toys.INGREDIENT 
         AND mrm.BOX_TYPE = 2)
     AND mrm.BOX_TYPE = 2
);

When you get to the toys and marmelades tables, you'll need a temporary holder table as @Gordon suggests.

Reusing large SQL queries in stored procedures

I am not sure if this would be the best way to do, but what came to my mind, is a variable cursor. You could do that using SYS_REFCURSOR. You can build a function that contains your query, and returns ref curosr. In all your procedures, you can just call that function. This will save you writing 150+ lines query in every procedure. More important, it will limit your program to one copy of the query, and therefore easy to maintain.

The function that returns the ref cursor, could be something like this:

CREATE OR REPLACE FUNCTION my_ugly_query() 
                           RETURN SYS_REFCURSOR
AS
  my_cursor_ref SYS_REFCURSOR;
BEGIN
  OPEN my_cursor_ref FOR
       SELECT -- 150+ lines of query;
  RETURN my_cursor_ref;
END;

This is how to use it:

CREATE OR REPLACE PACKAGE BODY MyPackage
as
PROCEDURE PopulateTable
IS 
  l_cur_refcur   SYS_REFCURSOR;
  s_array        fetch_array;
BEGIN
  l_cur_refcur := my_ugly_query();

  LOOP
    FETCH tran_cursor BULK COLLECT INTO s_array;
    EXIT when s_array%NOTFOUND;
    FORALL counter in 1..s_array.COUNT
        INSERT INTO my_table VALUES s_array(counter);
  END LOOP;  

  CLOSE my_cursor;
  COMMIT;

END PopulateTable;
END MyPackage;

Reuse a complex query result in other queries without redoing the complex query

You can loop through the query results like @phatfingers demonstrates (probably with a generic record variable or scalar variables instead of a rowtype, if the result type of the query doesn't match any existing rowtype). This is a good idea for few resulting rows or when sequential processing is necessary.

For big result sets your original approach will perform faster by an order of magnitude. It is much cheaper to do a mass INSERT / UPDATE / DELETE with one SQL command
than to write / delete incrementally, one row at a time.

A temporary table is the right thing for reusing such results. It gets dropped automatically at the end of the session. You only have to delete explicitly if you want to get rid of it right away or at the end of a transaction. I quote the manual here:

Temporary tables are automatically dropped at the end of a session, or
optionally at the end of the current transaction.

For big temporary tables it might be a good idea to run ANALYZE after they are populated.

Writeable CTE

Here is a demo for what Pavel added in his comment:

CREATE TEMP TABLE t1(id serial, txt text);
INSERT INTO t1(txt)
VALUES ('foo'), ('bar'), ('baz'), ('bax');

CREATE TEMP TABLE t2(id serial, txt text);
INSERT INTO t2(txt)
VALUES ('foo2'),('bar2'),('baz2');

CREATE TEMP TABLE t3 (id serial, txt text);

WITH x AS (
    UPDATE t1
    SET    txt = txt || '2'
    WHERE  txt ~~ 'ba%'
    RETURNING txt
    )
, y AS (
    DELETE FROM t2
    USING  x
    WHERE  t2.txt = x.txt
    RETURNING *
    )
INSERT INTO t3
SELECT *
FROM   y
RETURNING *;

Read more in the chapter Data-Modifying Statements in WITH in the manual.

How to reuse calculated columns avoiding duplicating the sql statement

You could try something like this.

SELECT 
    A.Val AS A, 
    B.Val AS B, 
    C.Val AS C 
FROM MYTABLE
  cross apply(select 1 + 2) as A(Val)
  cross apply(select A.Val + 3) as B(Val)
  cross apply(select B.Val * 7) as C(Val)

How to reuse a sub query in sql?

Use a Common Table Expression (CTE) if you're using SQL Server 2005+:

with cte as (
      select columns
      from result_set
      where condition_common
    )
select columns
from cte  as subset1
      join
      cte as subset2 
         on subset1.somekey = subset2.somekey
where otherconditions

Query optimization by reusing the inner query only once

Consider using WITH Clause:

WITH (
  SELECT (MIN(FlightDate), MAX(FlightDate), groupUniqArray(AirlineID)) /* tuple with required aggregates */
  FROM ontime2
  WHERE Origin='JFK'
) AS cte
SELECT
  FlightDate,
  FlightNum,
  Origin,
  Dest,
  AirlineID
FROM
  ontime1
WHERE 
  FlightDate >= cte.1 AND
  FlightDate <= cte.2 AND
  has(cte.3, AirlineID)
LIMIT 10;

---

Related Topics