Re: Multiple WHERE conditions - Not sure about this post

On Mar 13, 11:06 am, Mark D Powell <Mark.Pow...@xxxxxxx> wrote:
On Mar 13, 11:31 am, ame...@xxxxxxxxx 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 --

Mark,

Looks like your suggestion does 99% of what I need. The query is
below. Since I want to filter it to only groups with 2 records, I
need to find a way to do that. It is the last task.

Thanks for your idea.


SELECT customer_id, first_name, last_name, email, order_date,
order_id, product_id, code, status, subscr_type, cnt
FROM (SELECT c.customer_id, first_name, last_name, email, order_date,
ol.order_id, p.product_id, p.code, ol.status, subscr_type, COUNT(*)
OVER (PARTITION BY co.customer_id) cnt
FROM customer c, customer_account ca, engine.customer_order co,
engine.order_line ol, engine.product p,
product.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.subproduct_id = 197
AND p.code = ns.code
AND ns.subscr_type = 'Trial'
AND TRUNC(co.order_date) = TRUNC(SYSDATE - 60) AND ol.status
= 'Complete')
UNION
(SELECT c.customer_id, first_name, last_name, email, order_date,
ol.order_id, p.product_id, p.code, ol.status, subscr_type, COUNT(*)
OVER (PARTITION BY co.customer_id) cnt
FROM customer c, customer_account ca, engine.customer_order co,
engine.order_line ol, engine.product p,
product.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.subproduct_id = 197
AND p.code = ns.code
AND ns.subscr_type <> 'Trial'
AND TRUNC(co.order_date) = TRUNC(SYSDATE - 29) AND ol.status
<> 'Complete')
ORDER BY customer_id, order_id;


.

Relevant Pages

  • Re: Can I simpilify this query?
    ... Thanks...is the query actually adding the quantities and then checking the if result> 0? ... > join ords on product.number = ords.number ... > customer table that a product is supplied to. ... > UNION is because the ords table has 7 fields for the seven days of the week. ...
    (microsoft.public.sqlserver.programming)
  • Re: Sum of numbers
    ... the fields in the query to be laid out as they would be in your report while ... You say the union query 'only shows fields from the first table'. ... A field would be Customer Name. ...
    (microsoft.public.access.reports)
  • Re: Sum of numbers
    ... fine on unlinked tables and queries so long as they follow the Union Rules. ... In your query grid type ... Each customer is linked by that unique ID. ...
    (microsoft.public.access.reports)
  • Problems with OR in Oracle 8i
    ... reason the OR is causing queries to run dog slow. ... to take out the OR and use a UNION instead. ... select * from sales_order, customer where ... Almost every time I use an OR on a moderately complex query on a big table ...
    (comp.databases.oracle.server)
  • Re: Combining records from two queries
    ... combining UNION and SELECT queries to best effect. ... The list in each case has to have the same number & types of fields (for example, if the first Query begins with a Date/Time field, the second one should do so as well). ... I like to keep my Union Queries short and simple and do the rest of the work elsewhere. ... tblMentors comprises Subject Mentors and Professional Mentors and the Placement subform has a combo for each - the Subject Mentor combo puts the chosen MentorID in the SubjectMentorID field and the Professional Mentor combo puts the chosen MentorID in the ProfessionalMentorID field. ...
    (microsoft.public.access.queries)