Hola que tal amigos espero que se encuentren bien miren tengo el problema que usando union me devuelve la consulta con registros duplicados aqui esta la query:
select bst_leads_id, created_date, approved_date
from (
select l.orig_first_name,l.orig_last_name,l.orig_ge_employee_id,l.orig_email,l.orig_business,l.orig_t2_city,l.recv_t2_city,l.orig_t1_city,l.recv_t1_city,l.orig_region,l.recv_region,
l.orig_business_tier1,l.orig_business_tier2,l.orig_business_tier3,l.recv_business_tier1,l.recv_business_tier2,l.recv_business_tier3,
l.orig_parent_business,l.orig_parent_business_id,l.recv_parent_business,
l.recv_parent_business_id,l.recv_first_name,l.recv_last_name,l. recv_ge_employee_id,
l.recv_email,l.recv_business,l.cust_name,l.cont_name,l.cust_city,l.cust_state,l.cust_street,
l.cust_zip,l.cont_email,l.cont_phone,l.cust_industry,l.cust_industry_id,l.status,l.team,l.capital,
l.amount,l.leadbit,l.winbit,l.ibl,l.bst_leads_id,l.bst_person_originator,l.order_description,
l.bst_person_receiver,l.order_amount,l.bst_business_receiver,l.bst_location_originator,l.bst_person_lead_id,
TO_CHAR(l.order_date, 'MM-DD-YYYY HH24:MI:SS') as order_date,l.bst_location_receiver,l.bst_business_originator,l.bst_team_id,l.award,
l.progress,l.est_opportunity,TO_CHAR(l.est_close_date, 'MM-DD-YYYY HH24:MI:SS')as est_close_date,l.order_contract_number,l.approved_date,
l.description,TO_CHAR(l.created_date, 'MM-DD-YYYY HH24:MI:SS')as created_date,l.pay_requested_date,l.ok_to_pay,
TO_CHAR(l.modified_date, 'MM-DD-YYYY HH24:MI:SS')as modified_date,l.pay_confirmed_date,TO_CHAR(l.close_date, 'MM-DD-YYYY HH24:MI:SS')as close_date,l.promote_to_win,
l.bst_lead_kill_reason_id,l.bst_lead_status_id,l.kill_reason,l.kill_memo,
TO_CHAR(l.action_needed_date, 'MM-DD-YYYY HH24:MI:SS')as action_needed_date,l.requested_action,l.how_obtained,l.requested_lead,l.full_amount,
l.activity_log,l.lead_level,l.leadtype,l.eco_lead,l.eco_presentation,l.eco_multbiz,
ca.street1||decode(ca.street2,null,'',', '||ca.street2) cust_street_1, city.city cust_city_1, s.state cust_state_1, post.postal_code cust_zip_1, cont.name cont_name_1, cont.title cont_title, cont.phone cont_phone_1, cont.email cont_email_1, t.bst_location_id team_location_id, t.bst_industry_id team_industry_id, p.bst_location_id location_orig, leadbit leadflag, winbit winflag, amount winamount, est_opportunity estrev, decode(bst_or_ibl(l.bst_business_originator, l.bst_business_receiver, sysdate, l.bst_person_originator, l.bst_person_receiver),'BST', 'No', 'IBL', 'Yes') bst_ibl
from leads_view l, bst_team t, bst_customer_address ca, bst_customer_contact_leads cont, city_state city, bst_state_lookup s, postal_code_location post, bst_person p
where l.bst_team_id = t.bst_team_id
and l.bst_leads_id = cont.bst_leads_id and cont.contact_type = '1'
and cont.bst_customer_id = ca.bst_customer_id (+)
and ca.bst_city_state_id = city.bst_city_state_id (+)
and city.bst_state_lookup_id = s.bst_state_lookup_id (+)
and ca.postal_code_location_id = post.postal_code_location_id (+)
and p.bst_person_id(+) = l.BST_PERSON_ORIGINATOR
AND BST_SHOW_BUSINESS_GLO(l.BST_BUSINESS_RECEIVER) = '1'
AND BST_SHOW_BUSINESS_GLO(l.BST_BUSINESS_ORIGINATOR) = '1'
and (l.created_date >= to_date('1/1/2007','mm/dd/yyyy') and l.created_date < to_date('12/6/2007','mm/dd/yyyy')+1)
union
select l.orig_first_name,l.orig_last_name,l.orig_ge_employee_id,l.orig_email,l.orig_business,l.orig_t2_city,l.recv_t2_city,l.orig_t1_city,l.recv_t1_city,l.orig_region,l.recv_region,
l.orig_business_tier1,l.orig_business_tier2,l.orig_business_tier3,l.recv_business_tier1,l.recv_business_tier2,l.recv_business_tier3,
l.orig_parent_business,l.orig_parent_business_id,l.recv_parent_business,
l.recv_parent_business_id,l.recv_first_name,l.recv_last_name,l. recv_ge_employee_id,
l.recv_email,l.recv_business,l.cust_name,l.cont_name,l.cust_city,l.cust_state,l.cust_street,
l.cust_zip,l.cont_email,l.cont_phone,l.cust_industry,l.cust_industry_id,l.status,l.team,l.capital,
l.amount,l.leadbit,l.winbit,l.ibl,l.bst_leads_id,l.bst_person_originator,l.order_description,
l.bst_person_receiver,l.order_amount,l.bst_business_receiver,l.bst_location_originator,l.bst_person_lead_id,
TO_CHAR(l.order_date, 'MM-DD-YYYY HH24:MI:SS') as order_date,l.bst_location_receiver,l.bst_business_originator,l.bst_team_id,l.award,
l.progress,l.est_opportunity,TO_CHAR(l.est_close_date, 'MM-DD-YYYY HH24:MI:SS')as est_close_date,l.order_contract_number,l.approved_date,
l.description,TO_CHAR(l.created_date, 'MM-DD-YYYY HH24:MI:SS ')as created_date,l.pay_requested_date,l.ok_to_pay,
TO_CHAR(l.modified_date, 'MM-DD-YYYY HH24:MI:SS')as modified_date,l.pay_confirmed_date,TO_CHAR(l.close_date, 'MM-DD-YYYY HH24:MI:SS')as close_date,l.promote_to_win,
l.bst_lead_kill_reason_id,l.bst_lead_status_id,l.kill_reason,l.kill_memo,
TO_CHAR(l.action_needed_date, 'MM-DD-YYYY HH24:MI:SS')as action_needed_date,l.requested_action,l.how_obtained,l.requested_lead,l.full_amount,
l.activity_log,l.lead_level,l.leadtype,l.eco_lead,l.eco_presentation,l.eco_multbiz,
ca.street1||decode(ca.street2,null,'',', '||ca.street2) cust_street_1, city.city cust_city_1, s.state cust_state_1, post.postal_code cust_zip_1, cont.name cont_name_1, cont.title cont_title, cont.phone cont_phone_1, cont.email cont_email_1, t.bst_location_id team_location_id, t.bst_industry_id team_industry_id, p.bst_location_id location_orig, leadbit leadflag, winbit winflag, amount winamount, est_opportunity estrev, decode(bst_or_ibl(l.bst_business_originator, l.bst_business_receiver, sysdate, l.bst_person_originator, l.bst_person_receiver),'BST', 'No', 'IBL', 'Yes') bst_ibl
from leads_view l, bst_team t, bst_customer_address ca, bst_customer_contact_leads cont, city_state city, bst_state_lookup s, postal_code_location post, bst_person p
where l.bst_team_id = t.bst_team_id
and l.bst_leads_id = cont.bst_leads_id and cont.contact_type = '1'
and cont.bst_customer_id = ca.bst_customer_id (+)
and ca.bst_city_state_id = city.bst_city_state_id (+)
and city.bst_state_lookup_id = s.bst_state_lookup_id (+)
and ca.postal_code_location_id = post.postal_code_location_id (+)
and p.bst_person_id(+) = l.BST_PERSON_ORIGINATOR
AND BST_SHOW_BUSINESS_GLO(l.BST_BUSINESS_RECEIVER) = '1'
AND BST_SHOW_BUSINESS_GLO(l.BST_BUSINESS_ORIGINATOR) = '1'
and winbit = 1 and l.order_date >= to_date('1/1/2007','mm/dd/yyyy') and l.order_date < to_date('12/6/2007','mm/dd/yyyy')+1) l where bst_lead_status_id in ('4','6') and BST_LOCATION_ORIGINATOR in (select A.BST_LOCATION_ID from bst_location A where '739' = (SELECT bst_location_id FROM bst_location WHERE location_level = 1 START WITH bst_location_id = a.bst_location_id CONNECT BY PRIOR bst_parent_location_id = bst_location_id) union select A.BST_LOCATION_ID from bst_location A where '718' = (SELECT bst_location_id FROM bst_location WHERE location_level = 1 START WITH bst_location_id = a.bst_location_id CONNECT BY PRIOR bst_parent_location_id = bst_location_id)) order by bst_leads_id,created_date,approved_date

Espero que me puedan ayudar

Responder