Sql query returns error to Oracle linked query

• Stubby Bunny

  SSCommitted

  Points: 1733

  More actions

  October 31, 2013 at 9:26 am

  #281877

  I have a query that has to link to some oracle records through sql linked server. What the query does is pull up positions in a DB and sees what persons are placed in those positions. Then pulls information from Oracle tables. The problem I think is that if a person does not exist in that position it returns NULLS and the oracle side does not like this becuase it wants to see the SSN link. Anyway to get around this problem.

  selectmu.strUic as Unit,

  up.strPara + '\' + up.strLine + '\' + cast(intPositionNum as varchar(3)) as [Para\Line\Pos],

  up.strPosnTitle as Posn_Title,

  Case WHEN p.strPassName IS NULL THEN mn.strFullname else p.strPassName end Name,

  mn.strRank Rank,

  mn.strSSN SSN,

  case when strSctyClear in ('Y') then 'No' else 'Yes' end as REQCLR, '' as PassPortDT, '' as MobDay,

  Convert(varchar(10), s.DATE_SCTY_CLNC, 111) SCTYDT,

  --Substring(s.dtPhysical,1,4) + '/' + SUBSTRING(s.dtPhysical,5,6) PHY_EXAM_DT,

  Convert(varchar(10), mn.strEts, 111) [MRD\ETS],

  Convert(varchar(10), a.SRP_Dt, 111) SRP_DT,

  '' as CLS,

  '' as Profile,

  '' as Cleared,

  '' as Remarks

  from tblUnit mu

  LEFT JOIN tblUnitPosition up on up.intUnitMobId = mu.intUnitMobId

  LEFT JOIN tblUnitPersonnel p on p.intPositionId = up.intPositionID

  LEFT JOIN tblMnPersonnel mn on mn.strSSN = p.strSSN

  LEFT JOIN (Select SSN_SM, DATE_SCTY_CLNC, YR_MO_LAST_PHYS_EX from OPENQUERY(SIDPERS, 'Select SSN_SM, DATE_SCTY_CLNC, YR_MO_LAST_PHYS_EX from PERS_PERSON_TBL')) s on s.SSN_SM = p.strssn

  LEFT JOIN (Select SSN_SM from vw_SctyClnc) s on s.SSN_SM = p.strssn

  wheremu.intMobilizationId = 81

  Order by mu.strUICHiearchy, up.strpara, up.strLine, up.intPositionNum