Re: Eliminating Rows In Complex Query

On Oct 17, 8:26 pm, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
> Hi, been on this for more than 3 days.  In the below query I am
> looking to look at each group of orders for each customer.  Within
> that I only want the product type of 197 which is coded.  What I want
> to do next is to eliminate any groups or partitions that contain an
> 'Active' status.  With the remaining I only want to select a record if
> it has an expiration given by the date parameters.
>
> Not sure if that makes sense, but I want to get a list of customers
> that have no active orders and of the orders that are not active, at
> least one must be within the date range provided.
>
> I figured I have to use PARTITION BY to group the customers into
> groups in order to work on them separately.  I guess the trick here is
> to be able to eliminate the groups as a whole that fail any criteria.
>
> Been 4 days already and I continue to play:
>
> CURSOR zp_expired_crs(p_start_date DATE, p_end_date DATE) IS
> SELECT DISTINCT(customer_id), stat FROM (
>   SELECT DISTINCT(customer_id), SUM(DECODE(ol.status,'Active',1,0))
> stat, DENSE_RANK()
>   OVER (
>     PARTITION BY customer_id ORDER BY customer_id) stuff
>   FROM engine.order_line ol, engine.customer_order co, engine.product
> p
>   WHERE ol.order_id = co.order_id
>     AND ol.product_id = p.product_id
>     AND p.subproduct_id = 197
>     AND p.subproduct_id NOT IN (SELECT subproduct_id FROM
> customer.trading_services_lookup)
>     AND co.customer_id NOT IN (SELECT customer_id FROM subscriptions
> WHERE status = 1)
>     AND CALC_EXPIRATION_DATE(order_date, quantity, quantity_uom)
> BETWEEN p_start_date AND p_end_date
>   GROUP BY customer_id
>   HAVING SUM(DECODE(ol.status,'Active',1,0)) = 0);

Why not just specify all conditions in the WHERE clause and let Oracle figure out the best way to retrieve the data? And, by the way, what this is supposed to mean:

    AND p.subproduct_id = 197
    AND p.subproduct_id NOT IN (SELECT subproduct_id FROM customer.trading_services_lookup)

To me, this says "customer.trading_services_lookup must NOT contain subproduct_id=197". Is this what you really meant?

Regards,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Mon Oct 20 2008 - 01:05:18 CDT