Re: Nasty Query Here
From: patrick <pgovern_at_u.washington.edu>
Date: Fri, 11 Apr 2008 10:46:54 -0700 (PDT)
Message-ID: <b668746e-01e0-4377-9453-c445e524f79f@1g2000prf.googlegroups.com>
Date: Fri, 11 Apr 2008 10:46:54 -0700 (PDT)
Message-ID: <b668746e-01e0-4377-9453-c445e524f79f@1g2000prf.googlegroups.com>
Very confusing
query has customer_account ca
subquery has customer_address ca
while ca is now customer_account
May want to look at rewriting the subquery as exists/not exists
something like
and not exists (select 'x' from customer c2,
customer.subscriptions s2, customer.product p2, customer_address ca2, customer_account ct2, visitor v2 where c2.customer_id = c.customer_id and c2.email = c.email and ca2.customer_id = c2.customer_id and ca2.address_type_id = 1 and s2.customer_id = c2.customer_id and s2.status = 1 and s2.sell_rep_id IN (201, 202) and p2.produst_id = s2.produst_id and p2.produst_id = 1 and TRUNC(start_date) BETWEEN p_start_date AND p_end_date and ct2.customer_id = c2.customer_id and v.customer_id (+) = c2.customer_id)
Also are customer_account ct2 and visitor v2 really needed in the subquery.
====>Pat
Received on Fri Apr 11 2008 - 12:46:54 CDT