With Clause: Double Alias

Description: The WITH query_name clause provides a way assign a name to a subquery block. The subquery block can then be referenced in multiple places in the query by specifying the query name. The query is optimized by treating it as either an inline view or as a temporary table.

WITH <alias_one> AS
     (subquery_sql_statement)
          <alias_two> AS
     (sql_statement_from_alias_one)
SELECT <column_name_list>
FROM <alias_one>, <alias_two>
WHERE <join_condition>;
 
WITH RT1 AS
     (SELECT inst_id FROM gv$session),
          RT2 AS
     (SELECT UNIQUE inst_id FROM RT1
     UNION ALL
     SELECT UNIQUE inst_id FROM RT1)
SELECT /*+ MATERIALIZE */ *
FROM RT1, RT2
WHERE RT1.inst_id = RT2.inst_id;

Enjoyed this post? Share it!

 

Leave a comment

Your email address will not be published.