Query Improvement
From: Mtek <mtek_at_mtekusa.com>
Date: Wed, 16 Apr 2008 08:32:27 -0700 (PDT)
Message-ID: <3f88ce4a-4638-4ad4-8fb6-4439af846d72@m3g2000hsc.googlegroups.com>
Date: Wed, 16 Apr 2008 08:32:27 -0700 (PDT)
Message-ID: <3f88ce4a-4638-4ad4-8fb6-4439af846d72@m3g2000hsc.googlegroups.com>
Hi,
I posted this about a week ago and am still having issues trying to improve this query.
This is a nasty query here which I'm hoping to make smaller by using some joins. I got help from here before, so while I'm trying to do it, I was hoping some others can look at it also.
Thank you,
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, 'HOUSE' list_type 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' AND email NOT IN ( SELECT email FROM customer c, customer.subscriptions s, customer.product p, customer_address ca, customer_account ct, visitor v WHERE ca.address_type_id = 1 AND s.status = 1 AND s.sell_rep_id IN (201, 202) AND p.produst_id = 1 AND TRUNC(start_date) BETWEEN p_start_date AND p_end_date AND p.produst_id = s.produst_id AND c.customer_id = s.customer_id AND c.customer_id = ca.customer_id AND c.customer_id = ct.customer_id AND c.customer_id = v.customer_id (+)) UNION SELECT c.email || '|' || ca.first_name || '|' || ca.last_name ||Received on Wed Apr 16 2008 - 10:32:27 CDT
'|' || 'ZACKS' || '|' ||
TO_CHAR(c.date_registered, 'mmddrr hh24:mi:ss') || '|' || TO_CHAR(TRUNC(SYSDATE-1),'MM-DD-YYYY') || '|' || v.adid || '|' || cd.day_phone line, 'AMERITRADE' list_type 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' AND email IN ( SELECT email FROM customer c, customer.subscriptions s, customer.product p, customer_address ca, customer_account ct, visitor v WHERE ca.address_type_id = 1 AND s.status = 1 AND s.sell_rep_id IN (201, 202) AND p.produst_id = 1 AND TRUNC(start_date) BETWEEN p_start_date AND p_end_date AND p.produst_id = s.produst_id AND c.customer_id = s.customer_id AND c.customer_id = ca.customer_id AND c.customer_id = ct.customer_id AND c.customer_id = v.customer_id (+));