Re: Query Improvement

Only difference I see between the two queries   HOUSE if not email match
  AMERITRADE if a match

You may want to investigate something like

      SELECT c.email || '|' || ca.first_name || '|' || ca.last_name ||

              '|' || 'ZACKS' || '|' ||
               TO_CHAR(c.date_registered, 'mmddrr hh24:mi:ss') || '|'
||
             TO_CHAR(TRUNC(SYSDATE-1),'MM-DD-YYYY') || '|' ||
               v.adid || '|' || cd.day_phone line,
             case when not exists (select 'x'
                                     from  customer c2,
customer.subscriptions s,
                                           customer_address ca,
customer.product p
                                    where c2.customer_id =
c.customer_id
                                      and c2.email = c.email
                                      and ca.customer_id =
c.customer_id
                                      and ca.address_type_id = 1
                                      and s.customer_id =
c.customer_id
                                      AND s.status = 1
                                      AND s.sell_rep_id IN (201, 202)
                                      AND p.produst_id  = s.produst_id
                                      AND p.produst_id = 1
                                      AND TRUNC(start_date) BETWEEN
p_start_date AND p_end_date)
                        then 'HOUSE
                   else 'AMERITRADE'
             end
      FROM customer c, customer_account ca, visitor v,
           subscr_email_product s, customer_address cd
      WHERE c.date_registered BETWEEN p_start_date AND p_end_date
        AND c.customer_id = ca.customer_id(+)
        AND c.customer_id = s.customer_id
        AND c.customer_id = v.customer_id
        AND c.customer_id = cd.customer_id(+)
        AND s.email_product_id = 'HL'

====>Patrick
Received on Wed Apr 16 2008 - 11:19:55 CDT