George, This is the logic we use (Oracle) and it matches up the records pretty well:
LEFT JOIN LZ.GEOCODED_ADDRESS ON NVL(GEOCODED_ADDRESS.ARC_ADDRESS,'null') = NVL(TRIM(UPPER(PATIENT.ADD_LINE_1)),'null') AND NVL(GEOCODED_ADDRESS.ARC_CITY,'null') = NVL(TRIM(UPPER(PATIENT.CITY)),'null') AND NVL(GEOCODED_ADDRESS.ARC_STATE,'null') = NVL(TRIM(UPPER(zc_state.abbr)),'null') AND NVL(GEOCODED_ADDRESS.ARC_ZIP,'null') = NVL(TRIM(UPPER(patient.zip)),'null') I think we also upper cased all our addresses when we send them to be geocoded, which is why we don't have any UPPERs on the left side. You could also replace the NVLs with actual NULL checks but that makes the query quite a bit larger. Tim Tim Meyer Manager - Research Data Management Academic Health Center - Office of Information Systems IT Manager, CTSI-BPIC Phone: 612.624.8386 [email protected] On Tue, Aug 1, 2017 at 1:52 PM, Kowalski, George <[email protected]> wrote: > All, > > Anyone started matching the geocoded data to their patient addresses? It > does not seem as easy as matching the address, zip and city across the two > tables. > > G > > > George Kowalski BS > Biomedical Engineer > Biomedical Informatics > (414) 805-7318 (office) > [email protected]<mailto:[email protected]> > _______________________________________________ > Gpc-dev mailing list > [email protected] > http://listserv.kumc.edu/mailman/listinfo/gpc-dev >
_______________________________________________ Gpc-dev mailing list [email protected] http://listserv.kumc.edu/mailman/listinfo/gpc-dev
