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

Look up inline views in the Oracle docs, I think that'll let you do what you're trying to do...

Regards,
Dave

Dino Hsu <dino1_nospam_at_ms1.hinet.net> wrote in message news:s9ceitkcgb8eufi1rph75i8rf8v1687kpq_at_4ax.com...
> 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 - 21:43:50 CDT