How to reuse a large query without repeating it?

sql plsql dry
Buttons840 picture Buttons840 · Feb 2, 2012 · Viewed 16.7k times · Source

If I have two queries, which I will call horrible_query_1 and ugly_query_2, and I want to perform the following two minus operations on them:

(horrible_query_1) minus (ugly_query_2)
(ugly_query_2) minus (horrible_query_1)

Or maybe I have a terribly_large_and_useful_query, and the result set it produces I want to use as part of several future queries.

How can I avoid copying and pasting the same queries in multiple places? How can I "not repeat myself," and follow DRY principles. Is this possible in SQL?

I'm using Oracle SQL. Portable SQL solutions are preferable, but if I have to use an Oracle specific feature (including PL/SQL) that's OK.

Answer

René Nyffenegger picture René Nyffenegger · Feb 2, 2012 
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)

Related questions

SQL error "ORA-01722: invalid number"

A very easy one for someone, The following insert is giving me the ORA-01722: invalid number why? INSERT INTO CUSTOMER VALUES (1,'MALADY','Claire','27 Smith St Caulfield','0419 853 694'); INSERT INTO CUSTOMER VALUES (2,'GIBSON','Jake','27 Smith St Caulfield','0415 713 598…

Read More
Is there a combination of "LIKE" and "IN" in SQL?

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question. Further, I often use conditions like WHERE something …

Read More
ORA-06502: PL/SQL: numeric or value error: character string buffer too small

I tried the following code different ways, like by taking out the while or the if, but when I put both together (if and while), I always get the error at the end... undefine numero set serveroutput on accept numero …

Read More