Refactoring similiar subqueries with different WHERE clauses

Refactoring similiar subqueries with different WHERE clauses

pI have inherited a SQL query that is in desperate need of refactoring.
The query (6 pages long) looks something like this:/p precodeSELECT
(count(*) from tab1, tab2, tab3 where tab1.value1 is not null and
tab2.tab3id = tab3.id and tab3.value4 in (1,2,3) and tab3.value3 = 15 and
tab2.value2 = 10 [...]) as RESULT1, SELECT (sum(tab3.value5) from [EXACT
SAME QUERY AS ABOVE]) AS RESULT1_SUM SELECT (count(*) from tab1, tab2,
tab3 where tab1.value1 is not null and tab2.tab3id = tab3.id and
tab3.value4 in (1,2,3) and tab3.value3 = 15 and tab2.value2 = 27 [...]) as
RESULT2, ...[AD NAUSEAM for several more columns] UNION SELECT [same as
first query, but now tab1.value1 is NULL] ...[now sum()] FROM DUAL [of
course!] /code/pre pThe end result of this mess is a 2 row / 16 column
result, where the rows differ by whether or not tab1.value1 is null in the
(mostly copy and pasted) subquery and the columns differ by small
arbitrary variations in the other values of WHERE clause of the
subquery./p precode RESULT1 RESULT1_SUM RESULT2 RESULT2_SUM ...
IS_NOT_NULL 300 120000 90 80000 IS_NULL 70 90000 54 95000 /code/pre pI
must replicate this same output. My first instinct is to pull out the
common elements of the subquery into a WITH clause. But I'm stuck on how
to get the replicate the minor variations of WHERE clause - different
named columns element without using a separate subquery for each column.
/p pShould I just suck it up and dump the subquery into a global temporary
table or is there a better way?/p