Re: Multiple WHERE conditions - Not sure about this post

From: Mark D Powell <>
Date: Fri, 13 Mar 2009 09:06:00 -0700 (PDT)
Message-ID: <>

On Mar 13, 11:31 am, wrote:
> Hi,
> I killed my last post and am posting a new one with an updated query.
> Hopefully the other one was deleted.
> Anyhow, I want to retrieve 2 records based on different criteria and
> am hoping not to do a union or anything since 95% of the criteria is
> the same.
> SELECT customer_id, first_name, last_name, email, order_date,
> order_id, status, subscr_type
> FROM (SELECT c.customer_id, first_name, last_name, email, order_date,
> ol.order_id, 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 TRUNC(co.order_date) = TRUNC
> (SYSDATE - 60) AND ol.status =  'Complete')
>         AND (ns.subscr_type <> 'Trial' AND TRUNC(co.order_date) = TRUNC
> (SYSDATE - 29) AND ol.status <> 'Complete'))
> ORDER BY customer_id, order_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....

With a create table DDL with some sample data I am not going to spend much time on this but how about:
Write a query to find the customers whose trail is complete Write a query to find customers with active non-trail Place each query into the from clause as an inline view Join the two inline views in the query where clause on customer (and maybe product based on your example)

HTH -- Mark D Powell -- Received on Fri Mar 13 2009 - 11:06:00 CDT

Original text of this message