Re: Nasty Query Here

From: patrick <>
Date: Fri, 11 Apr 2008 10:46:54 -0700 (PDT)
Message-ID: <>

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

Received on Fri Apr 11 2008 - 12:46:54 CDT

Original text of this message