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 meye0...@umn.edu On Tue, Aug 1, 2017 at 1:52 PM, Kowalski, George <gkowal...@mcw.edu> 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) > gkowal...@mcw.edu<mailto:gkowal...@mcw.edu> > _______________________________________________ > Gpc-dev mailing list > Gpc-dev@listserv.kumc.edu > http://listserv.kumc.edu/mailman/listinfo/gpc-dev >
_______________________________________________ Gpc-dev mailing list Gpc-dev@listserv.kumc.edu http://listserv.kumc.edu/mailman/listinfo/gpc-dev
