How to share SQL in a multi-query SQL without creating objects?

Dear all,

In multi-query SQL's, we usually want parts of a SQL to be shared in different places in the same SQL or in different SQL's.

Ex.1: extracting different columns from the same set of sub-queris:

SQL1:
SELECT C1, C2, C3
FROM
(...) AS Q1,
(...) AS Q2,
(...) AS Q3

WHERE ...; SQL2:
SELECT C1, C4, C5
FROM
(...) AS Q1,
(...) AS Q2,
(...) AS Q3

WHERE ...; SQL1+2: (Q1, Q2, Q3 definitions are shared)

WITH Q1 AS (...), Q2 AS (...), Q3 AS (...)
SELECT C1, C2, C3 FROM Q1, Q2, Q3 WHERE ...;
SELECT C1, C4, C5 FROM Q1, Q2, Q3 WHERE ...;

The definitions of Q1, Q2, Q3 are complicated, so I don't want to have two copies of them.

Ex.2: joining results from differnt levels of summarizations (grouping) of the same set of joined base tables.

SELECT ...
FROM
(SELECT ...

    FROM T1, T2, T3
    WHERE W1
    GROUP BY G1) Q1,
(SELECT ...

    FROM T1, T2, T3
    WHERE W1
    GROUP BY G2) Q2,
(SELECT ...

    FROM T1, T2, T3
    WHERE W1
    GROUP BY G3) Q3
WHERE ...;     I wish the SELECT ... FROM T1, T2, T3 WHERE W1 part could be shared because they are the same except the GROUP BY clauses (different levels of summarizations).

Any comments? Oracle versions? Thanks in advance.

Dino Received on Wed Jun 13 2001 - 04:45:30 CDT