Re: Multiple WHERE conditions - Not sure about this post
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Fri, 13 Mar 2009 09:06:00 -0700 (PDT)
Message-ID: <9ab2c508-ba95-45fd-805f-c86605946d41_at_v19g2000yqn.googlegroups.com>
On Mar 13, 11:31 am, ame..._at_zacks.com 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....
Date: Fri, 13 Mar 2009 09:06:00 -0700 (PDT)
Message-ID: <9ab2c508-ba95-45fd-805f-c86605946d41_at_v19g2000yqn.googlegroups.com>
On Mar 13, 11:31 am, ame..._at_zacks.com 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