Re: Multiple WHERE conditions - Not sure about this post

From: Peter Nilsson <airia_at_acay.com.au>
Date: Sun, 15 Mar 2009 15:47:08 -0700 (PDT)
Message-ID: <cd95806b-0bfa-4993-8cb4-4f125995cc8a_at_v5g2000prm.googlegroups.com>

ame..._at_zacks.com wrote:
> ... 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.

But do you want two rows per customer?

> 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.

There are any number of ways to do this. A traditional existance query would work...

  select ...
    from customer c

    join customer_account ca on ...
   where exists (select 1 from ... where ...)
     and exists (select 1 from ... where ...)

> 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....

Analytical functions aren't necessary AFAICS. Here's an (obviously untested) group by with conditional count...

  select c.customer_id,

         first_name,
         last_name,
         email
    from c.customer c
    join customer_account      ca on ca.customer_id = c.customer_id
    join engine.customer_order co on co.customer_id = c.customer_id
    join engine.order_line     ol on ol.order_id = co.order_id
    join engine.product        p  on p.product_id = ol.product_id
    join product.newsletter_subscription
                               ns on ns.code = p.code

   where p.subproduct_id = 197
   group by

         c.customer_id,
         first_name,
         last_name,
         email
  having sum(
           case
             when ns.subscr_type = 'Trial'
              and ol.status = 'Complete'
              and trunc(co.order_date) = trunc(sysdate) - 60
             then 1
           end ) > 0
     and sum(
           case
             when ns.subscr_type <> 'Trial'
              and ol.status <> 'Complete'
              and trunc(co.order_date) = trunc(sysdate) - 30
             then 1
           end ) > 0

--
Peter

Received on Sun Mar 15 2009 - 17:47:08 CDT