vickawa
06 de Diciembre del 2007

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