pe.fdaddress has strange characters before the number. This is preventing my code from joining properly. Is there any way to remove these characters to run my code properly? The addresses results are null because of this. Thanks
SELECT ca.fdorgunit AS Facility
, pt.fdmedrecnum AS Account
, ca.fddos AS DOS
, pe.fddob AS DOB
, pe.fdssn AS SSN
, ad.fdaddr1 AS [Address]
FROM OPENQUERY (VISION, 'SELECT * FROM ci.tbcase') AS ca
LEFT JOIN OPENQUERY (VISION, 'SELECT * FROM de.tbpatient') AS pt
ON pt.id = ca.fdpatient
LEFT JOIN OPENQUERY (VISION, 'SELECT * FROM de.tbperson') AS pe
ON pt.fdperson = pe.fdid
LEFT JOIN OPENQUERY (VISION, 'SELECT * FROM de.tbaddress') AS ad
ON ad.id = pe.fdaddress
WHERE ca.fdcasestatus = 'Performed'
GROUP BY ca.fdorgunit, ca.fddos, pt.fdmedrecnum, pe.fdssn, pe.fddob, ad.fdaddr1
It's not nice to double post--you should clarify the original question.
Anywho... here is how to avoid that problem. see the change to your query. Also, I have no idea why you are using OPENQUERY here. You should look into LINKED SERVERS
SELECT ca.fdorgunit AS Facility
, pt.fdmedrecnum AS Account
, ca.fddos AS DOS
, pe.fddob AS DOB
, pe.fdssn AS SSN
, ad.fdaddr1 AS [Address]
FROM OPENQUERY (VISION, 'SELECT * FROM ci.tbcase') AS ca
LEFT JOIN OPENQUERY (VISION, 'SELECT * FROM de.tbpatient') AS pt
ON pt.id = ca.fdpatient
LEFT JOIN OPENQUERY (VISION, 'SELECT * FROM de.tbperson') AS pe
ON pt.fdperson = pe.fdid
LEFT JOIN OPENQUERY (VISION, 'SELECT * FROM de.tbaddress') AS ad
--here is the change
ON ad.id = LEFT(SUBSTRING(pe.fdaddress, PATINDEX('%[0-9.-]%', pe.fdaddress), 8000),PATINDEX('%[^0-9.-]%', SUBSTRING(pe.fdaddress, PATINDEX('%[0-9.-]%', pe.fdaddress), 8000) + 'X') -1)
WHERE ca.fdcasestatus = 'Performed'
GROUP BY ca.fdorgunit, ca.fddos, pt.fdmedrecnum, pe.fdssn, pe.fddob, ad.fdaddr1