ok....this first one works:

SELECT RTRIM(LTRIM(_MatterInfoVW2_1.lawfirm_code)) AS CLIENT_CD, RTRIM(LTRIM(_MatterInfoVW2_1.LAWFIRM_NAME)) AS CLIENT_NM,

RTRIM(LTRIM(_MatterInfoVW2_1.CLNT_MATT_CODE)) AS PROJECT_CD, RTRIM(LTRIM(_MatterInfoVW2_1.MATTER_NAME)) AS PROJECT_NM,

ISNULL(_MatterInfoVW2_1.TXT1, '') + ISNULL(_MatterInfoVW2_1.TXT2, '') + ISNULL(_MatterInfoVW2_1.TXT3, '') + ISNULL(_MatterInfoVW2_1.TXT4, '')

+ ISNULL(_MatterInfoVW2_1.TXT5, '') + ISNULL(_MatterInfoVW2_1.TXT6, '') AS DESCRIPTION, RTRIM(LTRIM(_MatterInfoVW2_1.OFFC))

AS LOCATION_CD, RTRIM(LTRIM(_MatterInfoVW2_1.CLNT_TYPE_CODE)) AS TYPE_CD, _MatterInfoVW2_1.OPEN_DATE,

_MatterInfoVW2_1.CLOSE_DATE, _MatterInfoVW2_1.ytd_fees AS FEES_AMOUNT

FROM Server2.DB2.dbo._MatterInfoVW2 AS _MatterInfoVW2_1 LEFT OUTER JOIN

INT_AUX_PROJECT ON _MatterInfoVW2_1.MATTER_NAME COLLATE SQL_Latin1_General_CP1_CI_AI = INT_AUX_PROJECT.PROJECT_NM AND

_MatterInfoVW2_1.CLNT_MATT_CODE COLLATE SQL_Latin1_General_CP1_CI_AI= INT_AUX_PROJECT.PROJECT_CD

WHERE (_MatterInfoVW2_1.MATTER_NAME IS NOT NULL) AND (_MatterInfoVW2_1.MATTER_NAME IS NOT NULL) AND

(NOT (RTRIM(LTRIM(_MatterInfoVW2_1.CLNT_MATT_CODE)) = '')) AND (NOT (RTRIM(LTRIM(_MatterInfoVW2_1.CLNT_MATT_CODE)) LIKE '%P%')) AND

(INT_AUX_PROJECT.PROJECT_CD IS NULL)

but this one does not:

select

rtrim(ltrim(MI.LAWFIRM_CODE)) as CLIENT_CD

,rtrim(ltrim(MI.CLNT_MATT_CODE)) as PROJECT_CD

,ROLE_CD = case (PI.PARTY_TYPE_CODE)

WHEN 'A' then 'Adverse'

WHEN 'C' then 'Matter Client'

WHEN 'F' then 'Friendly'

WHEN 'N' then 'Neutral'

WHEN 'P' then 'Potentially Adverse'

WHEN 'R' then 'Related'

WHEN 'U' then 'Unknown'

END

,IAL.LISTING_ID

,IAL.LISTING_SRC_ID

FROM Server2.DB2.dbo._PartyInfoVW3_NewContacts_Companies as PI INNER JOIN

Server2.DB2.dbo._MatterInfoVW2 AS MI ON PI.CLNT_MATT_CODE COLLATE SQL_Latin1_General_CP1_CI_AI = MI.CLNT_MATT_CODE COLLATE SQL_Latin1_General_CP1_CI_AI inner JOIN

INT_AUX_LISTING as IAL ON PI.NAME COLLATE SQL_Latin1_General_CP1_CI_AI = IAL.DISPLAY_NM

WHERE IAL.OWN_DIR_ID<>-3 and (NOT (PI.NAME IS NULL)) and(RTRIM(LTRIM(MI.CLNT_MATT_CODE)) NOT IN

(SELECT PROJECT_CD

FROM INT_AUX_PROJECT AS IAP

WHERE (DELETE_IND = 0))) and PI.name_type = 'o' and (not(rtrim(ltrim(MI.CLNT_MATT_CODE )) like '%P%'))