Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2009
    Posts
    1

    Red face Unanswered: left outer join need to change for 2005 but could not get accurate results.

    select
    PP.r_id,
    NAME = case when pp.name is null then p.NAME else pp.name end,
    pp_edate = case when pp.prop_edate is null then pr.PrP_EDATE else pp.prop_edate end,
    grp_nbr = case when pp.grp_nbr is null then p.GRP_NBR else pp.grp_nbr end,
    A.SIC_CD,
    C.csus_id,
    C.aff_id,
    C.DOB,
    C.AGE,
    C.SEX_CD,
    C.ZIP_CD,
    C.FIRST_NAME,
    C.LAST_NAME,
    C.MI_NAME,
    CO_CD = case when c.co_cd = 'AO' then 'O'

    else c.co_Cd end,
    prp.cls_ind,
    PP._ent_cd,
    pP.ss_ind,
    p.ra_ind,
    0 as 'tem_nbr'

    FROM aat PP with(NOLOCK), CS C with(NOLOCK), AL A with(NOLOCK),
    pt p with(NOLOCK), Pl prp with(NOLOCK), Cs_pp CP with(NOLOCK)
    WHERE A.ReSPECT_ID = XXX
    AND A.ReSPECT_ID *= PP.ReSPECT_ID AND prp.PRP_NUM = 0
    AND C.PRT_ID = A.PRT_ID AND
    CP.prp_id = C.prp_id AND CP.cs_id = C.cs_id AND CP.prp_num = prp.pp_num AND
    p.prp_id = A.prp_id AND prp.prospect_id = A.prp_id AND prp.prop_num *= pp.prop_num
    AND C.C_CD NOT IN ('AO','BH','KC')

  2. #2
    Join Date
    Dec 2008
    Posts
    135
    try like this
    Code:
    SELECT
    PP.r_id, 
    NAME = CASE WHEN pp.name IS NULL THEN p.NAME ELSE pp.name END, 
    pp_edate = CASE WHEN pp.prop_edate IS NULL THEN pr.PrP_EDATE ELSE pp.prop_edate END, 
    grp_nbr = CASE WHEN pp.grp_nbr IS NULL THEN p.GRP_NBR ELSE pp.grp_nbr END, 
    A.SIC_CD, C.csus_id, C.aff_id, C.DOB, C.AGE, C.SEX_CD, 
    C.ZIP_CD, C.FIRST_NAME, C.LAST_NAME, C.MI_NAME, 
    CO_CD = CASE WHEN c.co_cd = 'AO' THEN 'O' ELSE c.co_Cd END, 
    prp.cls_ind, PP._ent_cd, pP.ss_ind, p.ra_ind, 0 AS 'tem_nbr' 
    FROM aat PP WITH(NOLOCK)
    LEFT JOIN AL A WITH(NOLOCK) ON C.PRT_ID = A.PRT_ID AND A.ReSPECT_ID = XXX AND A.ReSPECT_ID = PP.ReSPECT_ID 
    LEFT JOIN pt p WITH(NOLOCK) ON p.prp_id = A.prp_id  
    LEFT JOIN Pl prp WITH(NOLOCK) ON prp.prospect_id = A.prp_id AND prp.PRP_NUM = 0 AND prp.prop_num = pp.prop_num
    LEFT JOIN Cs_pp CP WITH(NOLOCK) ON CP.prp_num = prp.pp_num 
    LEFT JOIN CS C WITH(NOLOCK) ON CP.prp_id = C.prp_id AND CP.cs_id = C.cs_id AND C.C_CD NOT IN ('AO','BH','KC')

  3. #3
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    I would convert everything over to ANSI style joins, cannot use compatibility mode 90 (2005) if you dont. Which means no PIVOT, etc.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •