Re: Multiple WHERE conditions - Not sure about this post
From: <amerar_at_zacks.com>
Date: Fri, 13 Mar 2009 09:12:56 -0700 (PDT)
Message-ID: <90844496-658c-4463-9609-923f2d0834b0_at_e18g2000yqo.googlegroups.com>
On Mar 13, 11:06 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> 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 --
Date: Fri, 13 Mar 2009 09:12:56 -0700 (PDT)
Message-ID: <90844496-658c-4463-9609-923f2d0834b0_at_e18g2000yqo.googlegroups.com>
On Mar 13, 11:06 am, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> 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 --
Worth a try...... Received on Fri Mar 13 2009 - 11:12:56 CDT