CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle With Clause
Version 11.1
 
WITH Clause
The WITH query_name clause lets you assign a name to a subquery block. You 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 either an inline view or as a temporary table.

You can specify this clause in any top-level SELECT statement and in most types of subqueries. The query name is visible to the main query and to all subsequent subqueries except the subquery that defines the query name itself.

A WITH clause is really best 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 Subquery Factoring:
  • You cannot nest this clause. That is, you cannot specify the subquery_factoring_clause within the subquery of another subquery_factoring_clause. However, a query_name defined in one subquery_factoring_clause can be used in the subquery of any subsequent subquery_factoring_clause.
  • In a query with set operators, the set operator subquery cannot contain the subquery_factoring_clause, but the FROM subquery can contain the subquery_factoring_clause.
 
Single alias WITH <alias_name> AS (subquery_sql_statement)
SELECT <column_name_list> FROM <alias>;
WITH q AS (SELECT dummy FROM DUAL)
SELECT dummy FROM q;

Double alias

(corrected per notes from (Colin Hart and Roy Fraties)
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 qb1 AS
  (SELECT inst_id FROM gv$session),
      qb2 AS
  (SELECT unique inst_id FROM qb1
   UNION ALL
   SELECT unique inst_id FROM qb1)
SELECT /*+ MATERIALIZE */ *
FROM qb1, qb2
WHERE qb1.inst_id = qb2.inst_id;
 
WITH Clause Demos

Code from PSOUG FMW mailing list
EXPLAIN PLAN FOR
SELECT per_h_email
FROM person p
WHERE p.per_ok2_email = 'A'
AND p.per_h_email IN (
  SELECT person_id
  FROM person
  WHERE per_fmw = 'Y'
)
UNION
SELECT po_w_email
FROM poie o
WHERE o.po_status = 'A'
AND o.po_w_email IN (
  SELECT person_id
  FROM person
  WHERE per_fmw = 'Y'
);

SELECT * FROM TABLE(dbms_xplan.display);

EXPLAIN PLAN FOR
WITH w AS (
  SELECT person_id
  FROM person
  WHERE per_fmw = 'Y')

SELECT per_h_email
FROM person p,
w
WHERE p.person_id =
w.person_id
AND p.per_ok2_email = 'A'
AND p.per_h_email IS NOT NULL
UNION
SELECT po_w_email
FROM poie o,
w
WHERE o.person_id =
w.person_id
AND o.po_status = 'A'
AND o.po_w_email IS NOT NULL;

SELECT * FROM TABLE(dbms_xplan.display);

WITH with CONNECT BY

Built from code posted by Michel Cadot at comp.databases.oracle.misc 28-Sep-2006
CREATE TABLE t1 (
pname VARCHAR2(10),
cases NUMBER(3));

INSERT INTO t1 VALUES ('Morgan',2);
INSERT INTO t1 VALUES ('Dan',3);
INSERT INTO t1 VALUES ('Jack',2);
INSERT INTO t1 VALUES ('Helen',1);
COMMIT;

SELECT * FROM t1;

CREATE TABLE t2 AS
SELECT pname FROM t1
WHERE 1=2;

SELECT * FROM t2;

INSERT INTO t2
WITH rn AS (
  SELECT rownum rn
  FROM DUAL
  CONNECT BY LEVEL <= (SELECT MAX(cases) FROM t1))
SELECT pname
FROM t1, rn
WHERE rn <= cases
ORDER BY pname;

SELECT * FROM t2;

Another WITH Clause with CONNECT BY from Michel Cadot
VARIABLE liste VARCHAR2(100)

EXECUTE :liste := '5, 25, 41, 52';

WITH liste AS (
  SELECT SUBSTR(:liste, INSTR(','||:liste||',', ',', 1, rn),
  INSTR(','||:liste||',', ',', 1, rn+1) - 
  INSTR(','||:liste||',', ',', 1, rn)-1) valeur
FROM (
  SELECT ROWNUM rn FROM DUAL
  CONNECT BY LEVEL<=LENGTH(:liste) - LENGTH(REPLACE(:liste,',',''))+1))

SELECT TRIM(valeur)
FROM liste;

Another WITH Clause demo based on code posted  by David Fitzjarrell at c.d.o.server 01-AUG-2006
CREATE TABLE test (
job_id   NUMBER(3),
batch_id NUMBER(3),
action   VARCHAR2(4),
actdate  DATE);

INSERT INTO test VALUES (1, 1, 'SENT', SYSDATE-5);
INSERT INTO test VALUES (2, 1, 'RECV', SYSDATE-4);
INSERT INTO test VALUES (3, 2, 'SENT', SYSDATE-3);
INSERT INTO test VALUES (4, 2, 'RECV', SYSDATE-2);
INSERT INTO test VALUES (5, 3, 'SENT', SYSDATE-1);

WITH col_generator AS (
SELECT t1.batch_id, DECODE(t1.action, 'SENT', t1.actdate) sent,
DECODE(t2.action,'RECV', t2.actdate)
received
FROM test t1, test t2
WHERE t2.batch_id(+) = t1.batch_id)
SELECT batch_id, max(sent) sent, max(received) received
FROM col_generator
GROUP BY batch_id
ORDER BY 1;
Another WITH Clause demo posted by Maxim Demenko to comp.databases. oracle.misc 3-Mar-2008.

This demo takes an input of numbers, in seconds and returns minutes:seconds.
col M:S format a10

WITH t AS (
  SELECT 100 s FROM DUAL
  UNION ALL
  SELECT 7201 FROM DUAL)
SELECT s,TRUNC(s/60)||':'||mod(s,60) "M:S"
FROM t;
 
Related Topics
Conditions
Connect By
Select
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [303 users online]    © 2010 psoug.org