Thanks for you help guys, your suggestion Ajay worked fine, cheers Dyfed Thomas Population Health Analyst DDI: 07 858 5967 Mob: 021 409 800
Midlands Health Network WEL House 711 Victoria Street PO Box 983 Hamilton 3240 Phone: 07 839 2888 Fax: 07 834 9242 From: Ajay Askoolum [via R] [mailto:ml-node+s789695n4281558...@n4.nabble.com] Sent: Tuesday, 10 January 2012 10:40 p.m. To: Dyfed Thomas Subject: Re: Extracting Data from SQL Server try: SELECT a.UNIQUE_ID, a.diag01 from LoadPUS a left join CVD_ICD10 b on a.diag01 = b.[ICD-10 Codes] or a.diag02 = b.[ICD-10 Codes] or a.diag03 = b.[ICD-10 Codes] I am not sure why your table name CVD_ICD10 has a suffix $. ________________________________ From: Jeff Newmiller <[hidden email]</user/SendEmail.jtp?type=node&node=4281558&i=0>> To: dthomas <[hidden email]</user/SendEmail.jtp?type=node&node=4281558&i=1>>; [hidden email]</user/SendEmail.jtp?type=node&node=4281558&i=2> Sent: Tuesday, 10 January 2012, 8:00 Subject: Re: [R] Extracting Data from SQL Server 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:<[hidden email]</user/SendEmail.jtp?type=node&node=4281558&i=3>> 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 <[hidden email]</user/SendEmail.jtp?type=node&node=4281558&i=4>> 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. > >______________________________________________ >[hidden email]</user/SendEmail.jtp?type=node&node=4281558&i=5> 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. ______________________________________________ [hidden email]</user/SendEmail.jtp?type=node&node=4281558&i=6> 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. [[alternative HTML version deleted]] ______________________________________________ [hidden email]</user/SendEmail.jtp?type=node&node=4281558&i=7> 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. ________________________________ If you reply to this email, your message will be added to the discussion below: http://r.789695.n4.nabble.com/Extracting-Data-from-SQL-Server-tp4281000p4281558.html To unsubscribe from Extracting Data from SQL Server, click here<http://r.789695.n4.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=4281000&code=ZHlmZWQudGhvbWFzQG1pZGxhbmRzaG4uaGVhbHRoLm56fDQyODEwMDB8NjI5Mzc3NzM2>. NAML<http://r.789695.n4.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.InstantMailNamespace&breadcrumbs=instant+emails%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml> ##################################################################################### Please Read the following: This message is for the named person's use only. It may...{{dropped:23}} ______________________________________________ R-help@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.
