Post reply

Union

  • July 15, 2003 at 2:43 pm

    #82724

    I want to remove duplicate records from the resultset even though i am using Union it's not merging the columns. here is the store procedure i am using and I want to merge FollowupType, CallType, and Job Type fields:

    select distinct p.people_id, p.gender,

    e.name as ethinicname,

    ep.closure_date,

    ep.intake_rating_fk as Intake_Rating, ep.closure_rating_fk as Closure_Rating,

    cltyp.name as call_type,

    st.site_name,

    ort.name as outcomeref,

    rr.name as outcomerefreason,

    ref.accepted as referralacceptance,

    rt.name as referraltype,

    rdt.name as Detailtype,

    ft.name as FollowupType,

    jt.name as jobtype,

    jdf.name as JobDysfunction,

    cr.name as Relationship

    from people p

    left outer join cases c ON p.people_id = c.people_fk

    left outer join v_cases_type cst ON cst.v_ct_id = c.cases_type_fk

    left outer join episode ep ON c.cases_id = ep.cases_fk

    left outer join v_outcome_referral_type ort ON ort.v_ort_id = ep.outcome_referral_type_fk

    left outer join v_outcome_referral_reason rr ON rr.v_orr_id = ep.outcome_referral_reasON_fk

    /*left outer join v_client_rating vcr ON vcr.v_cr_id = ep.intake_rating_fk

    left outer join v_client_rating vclr ON vclr.v_cr_id = ep.closure_rating_fk*/

    left outer join v_ethnic_group e ON p.ethnic_group_fk = e.v_eg_id

    left outer join service svc ON ep.episode_id = svc.episode_fk

    left outer join call cl ON cl.call_id = svc.call_fk

    left outer join v_call_type cltyp ON cltyp.v_ct_id = cl.call_type_fk

    left outer join site st ON st.site_id = p.site_fk

    left outer join contract_product_site cps ON st.site_id = cps.site_fk

    left outer join contract_product cp ON cp.contract_product_id = cps.contract_product_fk

    left outer join contract cc ON cc.contract_id = cp.contract_fk

    left outer join referral ref ON ref.referral_id = svc.referral_fk

    left outer join v_referral_type rt ON rt.v_rt_id = ref.referral_type_fk

    left outer join v_referral_detail_type rdt ON rdt.v_rdt_id = ref.referral_detail_type_fk

    left outer join event_note en ON cl.call_id = en.call_fk

    left outer join v_event_note_type ent on ent.v_ent_id = en.note_type_fk

    left outer join note_followup nf ON en.event_note_id = nf.event_note_fk

    left outer join v_followup_type ft ON ft.v_ft_id = nf.followup_type_fk

    left outer join v_job_type jt ON jt.v_jt_id = svc.job_type_fk

    left outer join v_job_dysfunction jdf ON jdf.v_jd_id = svc.job_dysfunction_fk

    left outer join v_contract_relation cr ON cr.v_cr_id = svc.contract_relatiON_fk

    left outer join authorizations auth ON auth.authorizations_id = svc.authorizations_fk

    left outer join event ev ON auth.authorizations_id =ev.authorizations_fk

    /*left outer join v_event_type et ON et.v_et_id = ev.event_type_fk

    left outer join v_delivery_method d ON d.v_dm_id = ev.delivery_method_fk*/

    where st.site_name like 'pfizer%' and ep.closure_date between '04-01-2003'

    and '06-30-2003' and c.status_fk='2' and p.deactivate is null and st.deactivate is null and

    c.deactivate is null and svc.deactivate is null and ref.deactivate is null

    and ep.deactivate is null and cltyp.name= 'Clinical' and p.people_id='4123583'

    group by

    p.people_id, p.gender,

    e.name,

    ep.closure_date,

    ep.intake_rating_fk, ep.closure_rating_fk,

    svc.years_of_service,

    cltyp.name,

    st.site_name,

    ort.name,

    rr.name,

    ref.accepted,

    rt.name,

    rdt.name,

    ft.name,

    jt.name,

    jdf.name,

    cr.name,

    en.note_type_fk

    Having en.note_type_fk='29'

    Union

    select distinct p.people_id, p.gender,

    e.name as ethinicname,

    ep.closure_date,

    ep.intake_rating_fk as Intake_Rating, ep.closure_rating_fk as Closure_Rating,

    cltyp.name as call_type,

    st.site_name,

    ort.name as outcomeref,

    rr.name as outcomerefreason,

    ref.accepted as referralacceptance,

    rt.name as referraltype,

    rdt.name as Detailtype,

    ft.name as FollowupType,

    jt.name as jobtype,

    jdf.name as JobDysfunction,

    cr.name as Relationship

    from people p

    left outer join cases c ON p.people_id = c.people_fk

    left outer join v_cases_type cst ON cst.v_ct_id = c.cases_type_fk

    left outer join episode ep ON c.cases_id = ep.cases_fk

    left outer join v_outcome_referral_type ort ON ort.v_ort_id = ep.outcome_referral_type_fk

    left outer join v_outcome_referral_reason rr ON rr.v_orr_id = ep.outcome_referral_reasON_fk

    /*left outer join v_client_rating vcr ON vcr.v_cr_id = ep.intake_rating_fk

    left outer join v_client_rating vclr ON vclr.v_cr_id = ep.closure_rating_fk*/

    left outer join v_ethnic_group e ON p.ethnic_group_fk = e.v_eg_id

    left outer join service svc ON ep.episode_id = svc.episode_fk

    left outer join call cl ON cl.call_id = svc.call_fk

    left outer join v_call_type cltyp ON cltyp.v_ct_id = cl.call_type_fk

    left outer join site st ON st.site_id = p.site_fk

    left outer join contract_product_site cps ON st.site_id = cps.site_fk

    left outer join contract_product cp ON cp.contract_product_id = cps.contract_product_fk

    left outer join contract cc ON cc.contract_id = cp.contract_fk

    left outer join referral ref ON ref.referral_id = svc.referral_fk

    left outer join v_referral_type rt ON rt.v_rt_id = ref.referral_type_fk

    left outer join v_referral_detail_type rdt ON rdt.v_rdt_id = ref.referral_detail_type_fk

    left outer join event_note en ON cl.call_id = en.call_fk

    left outer join v_event_note_type ent on ent.v_ent_id = en.note_type_fk

    left outer join note_followup nf ON en.event_note_id = nf.event_note_fk

    left outer join v_followup_type ft ON ft.v_ft_id = nf.followup_type_fk

    left outer join v_job_type jt ON jt.v_jt_id = svc.job_type_fk

    left outer join v_job_dysfunction jdf ON jdf.v_jd_id = svc.job_dysfunction_fk

    left outer join v_contract_relation cr ON cr.v_cr_id = svc.contract_relatiON_fk

    left outer join authorizations auth ON auth.authorizations_id = svc.authorizations_fk

    left outer join event ev ON auth.authorizations_id =ev.authorizations_fk

    where st.site_name like 'pfizer%' and ep.closure_date between '04-01-2003'

    and '06-30-2003' and c.status_fk='2' and p.deactivate is null and st.deactivate is null and

    c.deactivate is null and svc.deactivate is null and ref.deactivate is null

    and ep.deactivate is null and ft.name = 'Discontinued' and p.people_id='4123583'

    group by p.people_id, p.gender,

    e.name,

    ep.closure_date,

    ep.intake_rating_fk, ep.closure_rating_fk,

    svc.years_of_service,

    cltyp.name,

    st.site_name,

    ort.name,

    rr.name,

    ref.accepted,

    rt.name,

    rdt.name,

    ft.name,

    jt.name,

    jdf.name,

    cr.name,

    en.note_type_fk

    Union

    select distinct p.people_id, p.gender,

    e.name as ethinicname,

    ep.closure_date,

    ep.intake_rating_fk as Intake_Rating, ep.closure_rating_fk as Closure_Rating,

    cltyp.name as call_type,

    st.site_name,

    ort.name as outcomeref,

    rr.name as outcomerefreason,

    ref.accepted as referralacceptance,

    rt.name as referraltype,

    rdt.name as Detailtype,

    ft.name as FollowupType,

    jt.name as jobtype,

    jdf.name as JobDysfunction,

    cr.name as Relationship

    from people p

    left outer join cases c ON p.people_id = c.people_fk

    left outer join v_cases_type cst ON cst.v_ct_id = c.cases_type_fk

    left outer join episode ep ON c.cases_id = ep.cases_fk

    left outer join v_outcome_referral_type ort ON ort.v_ort_id = ep.outcome_referral_type_fk

    left outer join v_outcome_referral_reason rr ON rr.v_orr_id = ep.outcome_referral_reasON_fk

    /*left outer join v_client_rating vcr ON vcr.v_cr_id = ep.intake_rating_fk

    left outer join v_client_rating vclr ON vclr.v_cr_id = ep.closure_rating_fk*/

    left outer join v_ethnic_group e ON p.ethnic_group_fk = e.v_eg_id

    left outer join service svc ON ep.episode_id = svc.episode_fk

    left outer join call cl ON cl.call_id = svc.call_fk

    left outer join v_call_type cltyp ON cltyp.v_ct_id = cl.call_type_fk

    left outer join site st ON st.site_id = p.site_fk

    left outer join contract_product_site cps ON st.site_id = cps.site_fk

    left outer join contract_product cp ON cp.contract_product_id = cps.contract_product_fk

    left outer join contract cc ON cc.contract_id = cp.contract_fk

    left outer join referral ref ON ref.referral_id = svc.referral_fk

    left outer join v_referral_type rt ON rt.v_rt_id = ref.referral_type_fk

    left outer join v_referral_detail_type rdt ON rdt.v_rdt_id = ref.referral_detail_type_fk

    left outer join event_note en ON cl.call_id = en.call_fk

    left outer join v_event_note_type ent on ent.v_ent_id = en.note_type_fk

    left outer join note_followup nf ON en.event_note_id = nf.event_note_fk

    left outer join v_followup_type ft ON ft.v_ft_id = nf.followup_type_fk

    left outer join v_job_type jt ON jt.v_jt_id = svc.job_type_fk

    left outer join v_job_dysfunction jdf ON jdf.v_jd_id = svc.job_dysfunction_fk

    left outer join v_contract_relation cr ON cr.v_cr_id = svc.contract_relatiON_fk

    left outer join authorizations auth ON auth.authorizations_id = svc.authorizations_fk

    left outer join event ev ON auth.authorizations_id =ev.authorizations_fk

    where st.site_name like 'pfizer%' and ep.closure_date between '04-01-2003'

    and '06-30-2003' and c.status_fk='2' and p.deactivate is null and st.deactivate is null and

    c.deactivate is null and svc.deactivate is null and ref.deactivate is null

    and ep.deactivate is null and ft.name = 'Completed' and p.people_id='4123583'

    group by p.people_id, p.gender,

    e.name,

    ep.closure_date,

    ep.intake_rating_fk, ep.closure_rating_fk,

    svc.years_of_service,

    cltyp.name,

    st.site_name,

    ort.name,

    rr.name,

    ref.accepted,

    rt.name,

    rdt.name,

    ft.name,

    jt.name,

    jdf.name,

    cr.name,

    en.note_type_fk

    Union

    select distinct p.people_id, p.gender,

    e.name as ethinicname,

    ep.closure_date,

    ep.intake_rating_fk as Intake_Rating, ep.closure_rating_fk as Closure_Rating,

    cltyp.name as call_type,

    st.site_name,

    ort.name as outcomeref,

    rr.name as outcomerefreason,

    ref.accepted as referralacceptance,

    rt.name as referraltype,

    rdt.name as Detailtype,

    ft.name as FollowupType,

    jt.name as jobtype,

    jdf.name as JobDysfunction,

    cr.name as Relationship

    from people p

    left outer join cases c ON p.people_id = c.people_fk

    left outer join v_cases_type cst ON cst.v_ct_id = c.cases_type_fk

    left outer join episode ep ON c.cases_id = ep.cases_fk

    left outer join v_outcome_referral_type ort ON ort.v_ort_id = ep.outcome_referral_type_fk

    left outer join v_outcome_referral_reason rr ON rr.v_orr_id = ep.outcome_referral_reasON_fk

    /*left outer join v_client_rating vcr ON vcr.v_cr_id = ep.intake_rating_fk

    left outer join v_client_rating vclr ON vclr.v_cr_id = ep.closure_rating_fk*/

    left outer join v_ethnic_group e ON p.ethnic_group_fk = e.v_eg_id

    left outer join service svc ON ep.episode_id = svc.episode_fk

    left outer join call cl ON cl.call_id = svc.call_fk

    left outer join v_call_type cltyp ON cltyp.v_ct_id = cl.call_type_fk

    left outer join site st ON st.site_id = p.site_fk

    left outer join contract_product_site cps ON st.site_id = cps.site_fk

    left outer join contract_product cp ON cp.contract_product_id = cps.contract_product_fk

    left outer join contract cc ON cc.contract_id = cp.contract_fk

    left outer join referral ref ON ref.referral_id = svc.referral_fk

    left outer join v_referral_type rt ON rt.v_rt_id = ref.referral_type_fk

    left outer join v_referral_detail_type rdt ON rdt.v_rdt_id = ref.referral_detail_type_fk

    left outer join event_note en ON cl.call_id = en.call_fk

    left outer join v_event_note_type ent on ent.v_ent_id = en.note_type_fk

    left outer join note_followup nf ON en.event_note_id = nf.event_note_fk

    left outer join v_followup_type ft ON ft.v_ft_id = nf.followup_type_fk

    left outer join v_job_type jt ON jt.v_jt_id = svc.job_type_fk

    left outer join v_job_dysfunction jdf ON jdf.v_jd_id = svc.job_dysfunction_fk

    left outer join v_contract_relation cr ON cr.v_cr_id = svc.contract_relatiON_fk

    left outer join authorizations auth ON auth.authorizations_id = svc.authorizations_fk

    left outer join event ev ON auth.authorizations_id =ev.authorizations_fk

    /*left outer join v_event_type et ON et.v_et_id = ev.event_type_fk

    left outer join v_delivery_method d ON d.v_dm_id = ev.delivery_method_fk*/

    where st.site_name like 'pfizer%' and ep.closure_date between '04-01-2003'

    and '06-30-2003' and c.status_fk='2' and p.deactivate is null and st.deactivate is null and

    c.deactivate is null and svc.deactivate is null and ref.deactivate is null

    and ep.deactivate is null and p.people_id='4123583' and ref.referral_id is not null

    group by

    p.people_id, p.gender,

    e.name,

    ep.closure_date,

    ep.intake_rating_fk, ep.closure_rating_fk,

    svc.years_of_service,

    cltyp.name,

    st.site_name,

    ort.name,

    rr.name,

    ref.accepted,

    rt.name,

    rdt.name,

    ft.name,

    jt.name,

    jdf.name,

    cr.name,

    en.note_type_fk

    order by st.site_name, p.people_id

    Thanks in Advance

  • July 18, 2003 at 8:00 am

    This was removed by the editor as SPAM

  • July 21, 2003 at 6:35 am

    #466187

    Several things

    Why have 4 unions

    Why are you grouping on more fields than in distinct

    Why group and distinct

    What do you mean by merging, do you mean concatenate as in FollowupType+CallType+Job Type or do you mean you want distinct

    FollowupType, CallType, and Job Type.

    It would be easier to merge the unions into one

     select distinct p.people_id, 

    p.gender, 

    e.name as ethinicname, 

    ep.closure_date, 

    ep.intake_rating_fk as Intake_Rating, 

    ep.closure_rating_fk as Closure_Rating, 

    cltyp.name as call_type, 

    st.site_name, 

    ort.name as outcomeref, 

    rr.name as outcomerefreason, 

    ref.accepted as referralacceptance, 

    rt.name as referraltype, 

    rdt.name as Detailtype, 

    ft.name as FollowupType, 

    jt.name as jobtype, 

    jdf.name as JobDysfunction, 

    cr.name as Relationship 

    from people p 

    left outer join cases c ON p.people_id = c.people_fk 

    left outer join v_cases_type cst ON cst.v_ct_id = c.cases_type_fk

    left outer join episode ep ON c.cases_id = ep.cases_fk

    left outer join v_outcome_referral_type ort ON ort.v_ort_id = ep.outcome_referral_type_fk

    left outer join v_outcome_referral_reason rr ON rr.v_orr_id = ep.outcome_referral_reasON_fk

    left outer join v_ethnic_group e ON p.ethnic_group_fk = e.v_eg_id

    left outer join service svc ON ep.episode_id = svc.episode_fk 

    left outer join call cl ON cl.call_id = svc.call_fk 

    left outer join v_call_type cltyp ON cltyp.v_ct_id = cl.call_type_fk

    left outer join site st ON st.site_id = p.site_fk 

    left outer join contract_product_site cps ON st.site_id = cps.site_fk

    left outer join contract_product cp ON cp.contract_product_id = cps.contract_product_fk

    left outer join contract cc ON cc.contract_id = cp.contract_fk

    left outer join referral ref ON ref.referral_id = svc.referral_fk

    left outer join v_referral_type rt ON rt.v_rt_id = ref.referral_type_fk

    left outer join v_referral_detail_type rdt ON rdt.v_rdt_id = ref.referral_detail_type_fk

    left outer join event_note en ON cl.call_id = en.call_fk

    left outer join v_event_note_type ent on ent.v_ent_id = en.note_type_fk 

    left outer join note_followup nf ON en.event_note_id = nf.event_note_fk

    left outer join v_followup_type ft ON ft.v_ft_id = nf.followup_type_fk

    left outer join v_job_type jt ON jt.v_jt_id = svc.job_type_fk

    left outer join v_job_dysfunction jdf ON jdf.v_jd_id = svc.job_dysfunction_fk

    left outer join v_contract_relation cr ON cr.v_cr_id = svc.contract_relatiON_fk

    left outer join authorizations auth ON auth.authorizations_id = svc.authorizations_fk

    left outer join event ev ON auth.authorizations_id =ev.authorizations_fk

    where st.site_name like 'pfizer%' 

    and ep.closure_date between '04-01-2003' 

    and '06-30-2003' 

    and c.status_fk='2' 

    and p.deactivate is null 

    and st.deactivate is null 

    and c.deactivate is null 

    and svc.deactivate is null 

    and ref.deactivate is null 

    and ep.deactivate is null 

    and p.people_id='4123583'

    AND (

    (cltyp.name= 'Clinical' AND en.note_type_fk='29') 

    OR 

    ft.name = 'Discontinued' 

    OR 

    ft.name = 'Completed' 

    OR 

    ref.referral_id is not null

        )

    order by st.site_name, p.people_id

    I have put each unique part of where for each union at the end for clarity.

    Please supply more info as to what the output should look like.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • July 24, 2003 at 12:32 pm

    #466902

    "Max" solved the merging issue.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply