Oracle PL/SQL: WITH Clause: Double alias - The WITH query name clause provides a way assign a name to a subquery block. The subquery block can

Quick Search:

Oracle PL/SQL: WITH Clause: Double alias

Jump to:

Category: >> Oracle PL/SQL >> WITH Clause: Double alias

next >>

Snippet Name: 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.

Also see:
» AND Condition
» Distinct
» Having Clause
» EXISTS 2
» EXISTS
» WITH with CONNECT BY
» WITH Clause: Single alias

Comment: (none)

Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: February 26th, 2009

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;