Using WITH CLAUSE in oracle

The WITH clause lets us assign a name to a sub query section. Then we can then reference the subquery block multiple places in the query by specifying the query name.

Oracle optimizes the query by treating the query name as a temporary table or an inline view.

We can specify this clause in most types of sub queries and in any top level SELECT statement.

The query name is visible to the main query and to all subsequent sub queries except the sub query that defines the query name itself.

A WITH clause can be used better used when the result of the WITH query is required more than one time in the body of the query such as where one averaged value needs to be compared against two or three times.

Restrictions on Sub query Factoring:

    You cannot nest this clause.Means that, we cannot specify the subquery_factoring_clause within the sub query of another subquery_factoring_clause.

However, a query_name defined in one subquery_factoring_clause can be used in the subquery of any subsequent sub query_factoring_clause.

    In a query with set operators, the set operator subquery cannot contain the subquery_factoring_clause, but the FROM sub query can contain the subquery_factoring_clause.

Below is the demonstration of WITH clause in Oracle for calculating Number of hours between 2 dates. This is tested fine & can be readily implemented.

with a as(SELECT  (SYSDATE - CAST (MAX (lastmodified) AS DATE)) * 24  AS total_hours FROM    TABLE1 WHERE ID=23)
select round(total_hours) from a;

Below is the query which outputs the same value without using WITH CLAUSE:

SELECT  Round((SYSDATE - CAST (MAX (lastmodified) AS DATE)) * 24 ) AS total_hours
FROM    TABLE1 WHERE ID=23;