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 ||
'|' || '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 (+));

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
TO_CHAR(c.date_registered, 'mmddrr hh24:mi:ss') || '|'
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

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
TO_CHAR(c.date_registered, 'mmddrr hh24:mi:ss') || '|'
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

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
TO_CHAR(c.date_registered, 'mmddrr hh24:mi:ss') || '|'
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

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
TO_CHAR(c.date_registered, 'mmddrr hh24:mi:ss') || '|'
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