Re: Query that returns 2 different results in 9iRAC and 10gRac

From: Brian <brian_at_databaseknowledge.com>
Date: 9 Mar 2005 17:05:40 -0800
Message-ID: <1110416740.437082.170910@g14g2000cwa.googlegroups.com>

The "with" clause is fairly new, mostly everyone I've met has not seen it. Try it for yourself:

The query is:

with qw as ( select inst_id from gv$session),

         y as ( select unique inst_id from qw
                  union all
                  select unique inst_id from qw
                 )

select * from y

It materializes two temp views: qw and y. Once these are materialized it's possible to select out either of the views. This is one select statement.

You can do all kinds of things using the "with" clause. For example:

with q as (select * from dual)
select * from q;

with q as (select 1 from dual)
  select *
    from dual, q Received on Wed Mar 09 2005 - 19:05:40 CST