On Mar 12, 5:29*pm, The Magnet <a... (AT) unsu (DOT) com> wrote:
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:

1. A query might look more understandable when separating out complex
inline views

2. 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-.../dp/1430226684

On Mar 12, 8:03*pm, The Magnet <a... (AT) unsu (DOT) com> wrote:
This is something I forgot to mention in my initial post - your
approach is potentially flawed in general if there is more than a
single row with product_id != 204. You're incorrect that your UNION
will give you only two rows then (it will give you as many as there
are unique combinations of the values), and since your analytic
functions don't define a deterministic order within a CUSTOMER_ID
partition (the ORDER BY part should use something different than
CUSTOMER_ID) your result is indeterministic with your current
approach.

The PIVOT sample will simply pick the maximum value as defined in the
aggregate function which not be what you want. You can simply try the
different results by extending the sample data provided to more than
two rows.

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-.../dp/1430226684