How do I link to data on 2nd DB? 1

[glthornton]

Hi everyone, I'm working with 2 different SQL DB's on 2 separate servers running SQL 2000. What I am trying to do is extract diabetic patients from 1 DB based upon a zip code listing which is in another DB on a separate server. Here is the select statement that I've put together thus far:

SELECT DISTINCT pts.lastname,pts.firstname,vst.patientlastname,vst.patid,pts.statuscode
FROM Patients pts INNER JOIN Visits vst ON pts.patid = vst.PatID 
     INNER JOIN Assessments assess ON assess.vid = vst.vid
WHERE ((assess.Icd9Code = '250.00' or
assess.Icd9Code = '250.01'or
assess.Icd9Code = '250.02'or
assess.Icd9Code = '250.03'or
assess.Icd9Code = '250.01'or
assess.Icd9Code = '250.8'or
assess.Icd9Code = '250.82'or
assess.Icd9Code = '250.83'or
assess.Icd9Code = '251.8'or
assess.Icd9Code = '253.5'or
assess.Icd9Code = '790.2'or
assess.Icd9Code = '790.29') and (pts.statuscode <> '32768' and pts.statuscode <> '33025') and
(vst.patientlastname<>'Test' and
vst.patientlastname<>'' and
vst.patientlastname<>'Barber' and
vst.patientlastname<>'Milek' and
vst.patientlastname<>'Nichols' and
vst.patientlastname<>'Thatcher') and
(pts.zipcode like SELECT *
FROM OPENDATASOURCE('SQLOLEDB','Datasource=MAH-SQL1;User ID=sa;Password=').VTCCIS.dbo.VTDiabeticZC)))
ORDER BY pts.lastname,pts.firstname

As you can see at the end of the WHERE statement, that I am trying to do the comparison of the patients zip code (pts.zipcode) to that of the values it has stored in the other DB table on the other server. Can anyone point me in the right direction as to getting this to work? I have not worked with cross-linking databases like this before.

Any and all assistance would greatly be appreciated.

Kind Regards,
glthornton

 

[LNBruno]

Did a little "housecleaning" (all the ss's were making me cross-eyed!), but maybe this will help... Note the strikethroughs and specifying JUST the zipcode instead of all columns...

SELECT DISTINCT 
   p.lastname
   , p.firstname
   , v.patientlastname
   , v.patid
   , p.statuscode
FROM Patients p 
INNER JOIN Visits v 
   ON p.patid = v.PatID 
INNER JOIN aments a 
   ON a.vid = v.vid
WHERE a.Icd9Code IN ('250.00','250.01', '250.02'
      , '250.03', '250.01', '250.8', '250.82', '250.83'
      , '251.8', '253.5','790.2', '790.29') 
   AND (p.statuscode NOT IN ('32768', '33025')
   AND (v.patientlastname NOT IN ('Test', ''
      , 'Barber', 'Milek', 'Nichols', 'Thatcher')
   AND p.zipcode [s]LIKE[/s] IN
   (
   SELECT [b][s] * [/s][/b] ZipCode
   FROM OPENDATASOURCE('SQLOLEDB','Datasource=MAH-SQL1;User ID=sa;Password=').VTCCIS.dbo.VTDiabeticZC
[s]ORDER BY p.lastname,p.firstname[/s]
   )

< M!ke >
I am not a hamster and life is not a wheel.

 

[glthornton]

Mike you're awesome!! I changed my statement to the following:

SELECT DISTINCT pts.lastname,pts.firstname,vst.patid
FROM Patients pts INNER JOIN Visits vst ON pts.patid = vst.PatID 
     INNER JOIN Assessments assess ON assess.vid = vst.vid
WHERE assess.Icd9Code IN ('250.00','250.01','250.02','250.03','250.01','250.8','250.82','250.83','251.8','253.5','790.2','790.29') and 
pts.statuscode NOT IN ('32768','33025') and
(vst.patientlastname NOT IN ('Test','','Barber','Milek','Nichols','Thatcher')and
pts.zipcode IN( SELECT Zipcode
FROM OPENDATASOURCE('SQLOLEDB','Datasource=MAH-SQL1;User ID=sa;Password=').VTCCIS.dbo.VTDiabeticZC))

I'm working in the SQL Query Analyzer Tool and I know what you've provided me should work, but I'm getting the below error:

Server: Msg 18456, Level 14, State 1, Line 1
Login failed for user 'sa'.
[OLE/DB provider returned message: Invalid connection string attribute]

I know the Select statement for the second DB works perfectly fine as a stand alone statement. Here is another question for you in regards to this query. If a patient has a zip code of 05089-4500 and the second DB zip code field only has 05089, would this still work? Or do I need to change the IN statement to LIKE?

Thank you,
Glenn

 

[glthornton]

FYI, the zip code fields that I'm comparing are setup the same as a character field.

Glenn

 

[LNBruno]

I don't think I can help with your connection string problem...that's more of a local issue which your DBA should be able to help resolve.

As for the zip code question, no; if p.zipcode = '05089-4500' and that exact value doesn't exist in VTDiabeticZC, no record will be returned for that patient. You wouldn't be able to simply change the IN to LIKE because you'd need to specify wildcard positions for each zip code, so I'd suggest maybe pulling VTDiabeticZC zipcodes into a temp table/table datatype and doing a JOIN on it.

Which begs a question: Since you're not actually using anything from the linked table in your SELECT statement, what's the point?

< M!ke >
I am not a hamster and life is not a wheel.

 

[glthornton]

Thanks Mike. I'm understanding your level of thinking. See the purpose of this process is to find a way in which to only extract data for those patients that live in a certain geographic area. And I may be coming at this all wrong as well since when trying to compare zip code fields. As you know zip code fields obviously vary in size like we've mentioned before. Some have the zip extension and some don't. And that seems to be the bottleneck. I may need to re-think a better way to approach this. You're assistance in providing me a better structured SQL statement has been awesome!! Thank you!!

Glenn

 

[glthornton]

Mike,

I was able to extract the 5 characters of the zip code from the pts.zipcode by using the following syntax:

Left(pts.zipcode,5)

I know this is now working as well. Thanks again for your assistance.

Glenn