Look for best practices for writing SQL in Oracle when the same subquery is used twice

Question

I am writing SQL in Oracle. In SQL, I have the same subquery that is used twice. I am trying to find a better way to replace such common subqueries or queries that are reused.

I was thinking of creating a view for the subquery, but I am not allowed to do this. Global temporary tables may be another solution, but I found out that such tables are not designed to be created and deleted on the fly.

Answer 1

You can use a view or a global temporary table, but it is often better to use a WITH clause, for example:

WITH q AS (SELECT something FROM mytable) SELECT something FROM q UNION ALL SELECT something+1 FROM q;