Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How to avoid a temporary table
In article <9769adc8.0310230450.4a46090d_at_posting.google.com>, plm_at_gmx.li
says...
> Hello,
>
> Often I have seen opinions in this group that temporary tables are not
> necessary and can always be avoided. I don't like (the oracle way of)
> temporary tables and would like to avoid them, if possible. However I
> feel that often they are unavoidable in order to "modularize" SQL,
> i.e. avoid multiple occurrance of similar pieces of SQL.
>
> I know that you can always rewrite a temporary table as a subquery.
> Often however, you end up using multiple subqueries that are very
> similar, i.e. duplication of "code", that you avoid in "normal"
> programming languages by using subfunctions that calculate
> intermediate results and store these.
>
> Am I missing some approach or method to avoid code duplication without
> resorting to temporary tables?
>
Oracle 9i has the new WITH clause for SQL, which lets you modularize
repeating SELECTS. Search the SQL docs for "subquery_factoring_clause",
it should help.