Hi,

I've at this for a couple of days and have had no luck looking on the
internet. We have 10gR2. I have this query:

SELECT customer_id, first_name, last_name, email, order_date, status,
subscr_type
FROM (SELECT c.customer_id, first_name, last_name, email, order_date,
p.product_id, p.code, ol.status, subscr_type, ROW_NUMBER()
OVER (PARTITION BY c.customer_id ORDER BY c.customer_id) cnt
FROM customer c, customer_account ca, customer_order co,
order_line ol, product p, .newsletter_subscription ns
WHERE c.customer_id = co.customer_id
AND c.customer_id = ca.customer_id
AND co.order_id = ol.order_id
AND ol.product_id = p.product_id
AND p.code = ns.code
AND p.subproduct_id = 197
AND ns.subscr_type = 'Trial'
AND ol.status = 'Complete'
AND TRUNC(co.order_date) = TRUNC(SYSDATE - 60))
ORDER BY customer_id;

But, what I am really looking for is that I want customers who have
purchased a 'Trial' 60 days ago with a status of 'Complete', and ALSO
have on their account a 'Non-Trial' order with a status of 'Active' or
Cancelled' purchased 30 days ago.

So, customers who have a 'Complete' Trial ordered 60 days ago, and now
are 'Active' or 'Cancelled' with a Non-Trial ordered 30 days ago.

I'm hoping to do this in one query using analytical functions of
something, rather than some union or PL/SQL code....

On Fri, 13 Mar 2009 08:02:24 -0700 (PDT), amerar (AT) zacks (DOT) com wrote:

as this newsgroup has always saved my butt.

Just to get those lazy grey cells working:
Apparently you have two sets which can be joined by a condition: which
means two inline views.

No, and I'm not going to do it, as your payment is insufficient.

--
Sybrand Bakker
Senior Oracle DBA