Re: Multiple selects in one
Date: Sat, 13 Mar 2010 03:41:59 -0800 (PST)
Message-ID: <d7c9f974-7690-49c4-9fff-814e0f665b1a_at_u9g2000yqb.googlegroups.com>
On Mar 12, 5:29 pm, The Magnet <a..._at_unsu.com> wrote:
> > Interesting query. It works. I never really understood the 'WITH' > queries. Reading on it did not really help either.
As mentioned by others, the WITH clause is only used here to generate some test data, it has nothing to do with the PIVOT operation performed.
In general understanding the WITH clause (also called "Subquery factoring" or "Common Table Expression (CTE)") is not complicated - it is just another way of writing an inline view, so you could rewrite my sample query without changing its meaning using an inline view like that:
SELECT
customer_id
, non_ut_subscr_id
, non_ut_product_id
, non_ut_status
, ut_subscr_id
, ut_product_id
, ut_status
FROM
(
SELECT
customer_id
, max(decode(product_id, 204, null, subscription_id)) as
non_ut_subscr_id
, max(decode(product_id, 204, null, product_id)) as
non_ut_product_id
, max(decode(product_id, 204, null, status)) as
non_ut_status
, max(decode(product_id, 204, subscription_id, null)) as
ut_subscr_id
, max(decode(product_id, 204, product_id, null)) as
ut_product_id
, max(decode(product_id, 204, status, null)) as
ut_status
FROM
(
select
12345 as CUSTOMER_ID
, 203 as PRODUCT_ID
, 6767 as SUBSCRIPTION_ID
, 2 as STATUS
from
dual
union all
select
12345 as CUSTOMER_ID
, 204 as PRODUCT_ID
, 6768 as SUBSCRIPTION_ID
, 1 as STATUS
from
dual
) subscriptions
WHERE
customer_id = 12345
GROUP BY
customer_id
)
;
The added value of the WITH clause is many-fold:
- A query might look more understandable when separating out complex inline views
- It is best suited for a "peel the onion" or "divide-and-conquer" approaches where each subquery is based on one or more of the previous ones, and is in particular helpful if the same view is used more than once. You'll agree that the following query needs to include the view "a" only once and is better readable:
with a as (
select
x
, y
from
tab_z
),
b as (
select
...
from
a
where
x != y
),
c as (
select
...
from
b
, a
where
a.x > b.y
)
select
...
from
c
than this one, which has the same meaning:
select
...
from
(
select
...
from
(
select
...
from
(
select
x
, y
from
tab_z
) a
where
x != y
) b
, (
select
x
, y
from
tab_z
) a
where
a.x > b.y
) c;
Notice in particular that I had to write the view "a" twice when using regular inline views.
3. Not only it looks nicer, but Oracle is going to attempt to materialize (by creating a system generated global temporary table on the fly) the view results if the view is used more than once, so in my example above the view "a" would get instantiated as a global temporary table and Oracle needs to execute the query of the inline view only once, rather than twice - of course there is an overhead of writing the and re-reading the global temporary table.
4. The SQL standard defines recursive WITH clauses that have been supported by other vendors and now is also supported by Oracle 11.2 (with some limitations). In other database systems the recursive WITH clause could be used to serve similar purposes as the hierarchical query in Oracle (CONNECT BY ... START WITH) , but it is not limited to that.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
Co-author of the "OakTable Expert Oracle Practices" book: http://www.apress.com/book/view/1430226684 http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684 Received on Sat Mar 13 2010 - 05:41:59 CST