SELECT b.*,
party_type
FROM hz_parties party,
(SELECT b.contact_party_id,
max(a.lead_date) creation_date,
'LEADS' load_type
FROM as_sales_leads a,
as_sales_lead_contacts b
WHERE a.sales_lead_id = b.sales_lead_id
AND lead_date > SYSDATE -$DAY
GROUP BY contact_party_id
UNION
SELECT a.contact_party_id,
max(a.hist_date) creation_date,
'OPPS' load_type
FROM (SELECT b.contact_party_id,
to_date(a.attribute4, 'mm/dd/yyyy') hist_date
FROM as_leads_all a,
as_lead_contacts_all b
WHERE a.lead_id = b.lead_id
AND a.attribute4 LIKE '__/__/____%'
UNION
SELECT b.contact_party_id,
to_date(a.attribute4, 'yyyy-mm-dd HH24:MI:SS') hist_date
FROM as_leads_all a,
as_lead_contacts_all b
WHERE a.lead_id = b.lead_id
AND a.attribute4 LIKE '____-__-__%'
UNION
SELECT b.contact_party_id,
a.creation_date hist_date
FROM as_leads_all a,
as_lead_contacts_all b
WHERE a.lead_id = b.lead_id
AND a.attribute4 IS NULL ) a
WHERE a.hist_date > SYSDATE -$DAY
GROUP BY a.contact_party_id
UNION
SELECT a.party_id contact_party_id,
max(a.hist_date) creation_date,
'QUOTES' load_type
FROM (SELECT party_id,
to_date(replace(quote_description || ' ', '.', '/'), 'dd/mm/yyyy HH24:MI:SS') hist_date
FROM aso_quote_headers_all
WHERE quote_description LIKE '__.__.____%'
UNION
SELECT party_id,
to_date(quote_description, 'mm/dd/yyyy') hist_date
FROM aso_quote_headers_all
WHERE quote_description LIKE '__/__/____%'
UNION
SELECT party_id,
to_date(quote_description, 'yyyy/mm/dd HH24:MI:SS') hist_date
FROM aso_quote_headers_all
WHERE quote_description LIKE '____/__/__%'
UNION
SELECT party_id,
creation_date hist_date
FROM aso_quote_headers_all
WHERE quote_description IS NULL ) a
WHERE a.hist_date > SYSDATE -$DAY
GROUP BY a.party_id
UNION
SELECT b.party_id contact_party_id,
max(a.creation_date) creation_date,
'SR' load_type
FROM cs_incidents_all_b a,
cs_hz_sr_contact_points b
WHERE a.incident_id = b.incident_id
AND a.creation_date > SYSDATE -$DAY
AND b.primary_flag = 'Y'
GROUP BY b.party_id
UNION
SELECT c.note_context_type_id contact_party_id,
max(n.creation_date) creation_date,
'Note' load_type
FROM jtf_notes_b n,
jtf_note_contexts_v c
WHERE n.jtf_note_id = c.jtf_note_id
AND n.note_type IN ('KI_CNN', 'SALES')
AND c.note_context_type IN ('PARTY', 'PARTY_RELATIONSHIP')
AND n.creation_date > SYSDATE -$DAY
GROUP BY c.note_context_type_id) b
WHERE party.party_id = b.contact_party_id
AND party.party_type IN ('PERSON', 'PARTY_RELATIONSHIP');
一眼看上去有很多的union,觉得很不爽,而且执行速度不怎么样,请高手帮助优化!万分感谢!
party_type
FROM hz_parties party,
(SELECT b.contact_party_id,
max(a.lead_date) creation_date,
'LEADS' load_type
FROM as_sales_leads a,
as_sales_lead_contacts b
WHERE a.sales_lead_id = b.sales_lead_id
AND lead_date > SYSDATE -$DAY
GROUP BY contact_party_id
UNION
SELECT a.contact_party_id,
max(a.hist_date) creation_date,
'OPPS' load_type
FROM (SELECT b.contact_party_id,
to_date(a.attribute4, 'mm/dd/yyyy') hist_date
FROM as_leads_all a,
as_lead_contacts_all b
WHERE a.lead_id = b.lead_id
AND a.attribute4 LIKE '__/__/____%'
UNION
SELECT b.contact_party_id,
to_date(a.attribute4, 'yyyy-mm-dd HH24:MI:SS') hist_date
FROM as_leads_all a,
as_lead_contacts_all b
WHERE a.lead_id = b.lead_id
AND a.attribute4 LIKE '____-__-__%'
UNION
SELECT b.contact_party_id,
a.creation_date hist_date
FROM as_leads_all a,
as_lead_contacts_all b
WHERE a.lead_id = b.lead_id
AND a.attribute4 IS NULL ) a
WHERE a.hist_date > SYSDATE -$DAY
GROUP BY a.contact_party_id
UNION
SELECT a.party_id contact_party_id,
max(a.hist_date) creation_date,
'QUOTES' load_type
FROM (SELECT party_id,
to_date(replace(quote_description || ' ', '.', '/'), 'dd/mm/yyyy HH24:MI:SS') hist_date
FROM aso_quote_headers_all
WHERE quote_description LIKE '__.__.____%'
UNION
SELECT party_id,
to_date(quote_description, 'mm/dd/yyyy') hist_date
FROM aso_quote_headers_all
WHERE quote_description LIKE '__/__/____%'
UNION
SELECT party_id,
to_date(quote_description, 'yyyy/mm/dd HH24:MI:SS') hist_date
FROM aso_quote_headers_all
WHERE quote_description LIKE '____/__/__%'
UNION
SELECT party_id,
creation_date hist_date
FROM aso_quote_headers_all
WHERE quote_description IS NULL ) a
WHERE a.hist_date > SYSDATE -$DAY
GROUP BY a.party_id
UNION
SELECT b.party_id contact_party_id,
max(a.creation_date) creation_date,
'SR' load_type
FROM cs_incidents_all_b a,
cs_hz_sr_contact_points b
WHERE a.incident_id = b.incident_id
AND a.creation_date > SYSDATE -$DAY
AND b.primary_flag = 'Y'
GROUP BY b.party_id
UNION
SELECT c.note_context_type_id contact_party_id,
max(n.creation_date) creation_date,
'Note' load_type
FROM jtf_notes_b n,
jtf_note_contexts_v c
WHERE n.jtf_note_id = c.jtf_note_id
AND n.note_type IN ('KI_CNN', 'SALES')
AND c.note_context_type IN ('PARTY', 'PARTY_RELATIONSHIP')
AND n.creation_date > SYSDATE -$DAY
GROUP BY c.note_context_type_id) b
WHERE party.party_id = b.contact_party_id
AND party.party_type IN ('PERSON', 'PARTY_RELATIONSHIP');
一眼看上去有很多的union,觉得很不爽,而且执行速度不怎么样,请高手帮助优化!万分感谢!
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货