R help - Jan 2012 - Extracting Data from SQL Server

Hi, 

I am new to R (and rusty on SQL!) and I'm trying to extract records from a
SQL server database. I have a table of patient records (LoadPUS) which have
three code columns which i want to evaluate against a list of particular
codes (CVD_ICD$ table). Given the size of the patient table I want to
restrict the data I pull into R to the data I only want to analyse so I am
using SQL to do this. The code i have is as follows:

library(RODBC)
channel<-odbcConnect("NatCollections")
query<-"SELECT UNIQUE_ID, diag01 from LoadPUS 
WHERE (diag01 IN (SELECT [ICD-10 Codes] From CVD_ICD10$)) OR (diag02 IN
(SELECT [ICD-10 Codes] From CVD_ICD10$))
OR (diag03 IN (SELECT [ICD-10 Codes] From CVD_ICD10$))"

This returns duplicate values, I don't want to hardcode the values because
it is quite a long list. Running the "IN" function just for
"diag01" returns
the correct number of records, however when combining with another
"IN"
function it doesn't return the correct number of records. Can you see where
my SQL is incorrect or is there another way of doing this?

Much appreciated,
D

--
View this message in context:
http://r.789695.n4.nabble.com/Extracting-Data-from-SQL-Server-tp4281000p4281000.html
Sent from the R help mailing list archive at Nabble.com.

This is OT here. However, you might want to investigate the UNIQUE keyword in
the SQL Server documentation for SELECT.
---------------------------------------------------------------------------
Jeff Newmiller                        The     .....       .....  Go Live...
DCN:<jdnewmil at dcn.davis.ca.us>        Basics: ##.#.       ##.#.  Live
Go...
                                      Live:   OO#.. Dead: OO#..  Playing
Research Engineer (Solar/Batteries            O.O#.       #.O#.  with
/Software/Embedded Controllers)               .OO#.       .OO#.  rocks...1k
--------------------------------------------------------------------------- 
Sent from my phone. Please excuse my brevity.

dthomas <dyfed.thomas at midlandshn.health.nz> wrote:
>Hi, 
>
>I am new to R (and rusty on SQL!) and I'm trying to extract records
>from a
>SQL server database. I have a table of patient records (LoadPUS) which
>have
>three code columns which i want to evaluate against a list of
>particular
>codes (CVD_ICD$ table). Given the size of the patient table I want to
>restrict the data I pull into R to the data I only want to analyse so I
>am
>using SQL to do this. The code i have is as follows:
>
>library(RODBC)
>channel<-odbcConnect("NatCollections")
>query<-"SELECT UNIQUE_ID, diag01 from LoadPUS 
>WHERE (diag01 IN (SELECT [ICD-10 Codes] From CVD_ICD10$)) OR (diag02 IN
>(SELECT [ICD-10 Codes] From CVD_ICD10$))
>OR (diag03 IN (SELECT [ICD-10 Codes] From CVD_ICD10$))"
>
>This returns duplicate values, I don't want to hardcode the values
>because
>it is quite a long list. Running the "IN" function just for
"diag01"
>returns
>the correct number of records, however when combining with another
"IN"
>function it doesn't return the correct number of records. Can you see
>where
>my SQL is incorrect or is there another way of doing this?
>
>Much appreciated,
>D
>
>--
>View this message in context:
>http://r.789695.n4.nabble.com/Extracting-Data-from-SQL-Server-tp4281000p4281000.html
>Sent from the R help mailing list archive at Nabble.com.
>
>______________________________________________
>R-help at r-project.org mailing list
>https://stat.ethz.ch/mailman/listinfo/r-help
>PLEASE do read the posting guide
>http://www.R-project.org/posting-guide.html
>and provide commented, minimal, self-contained, reproducible code.