0

I set-up a connection from R to (Microsoft SQL Server Studio) using the RODBC package. While I am able to run simple SQL queries directly from R, I find that the more complex sql queries containing special characters such as "@" for declaring a table while creating a temp table tend to return an error from R. I have tried to escape this within R itself (by placing it in quotes) , however, this is failing, as SQL could not interpret this escape characters (I guess).

My goal is to perform soundex/fuzzy matching of some client records again the clients in the database (~3M rows). I have tried getting this done directly using the stringdist package in R but the matching process is blowing out my RAM (16GB), hence, the reason why I have resulted to matching the data from within SQL itself. I could have easily done this in SQL, however, I need to set-up this in R so that non-technical individuals can easily run the R script and query, database and perform further work on the resulting dataset.

I have tried the suggestion in this post but did not find it helpful to resolve this issue

Any tips on how to escape SQL special characters like the @ symbol would be useful.

I get this error in R:

1" 42000 102 [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'go'."
2 "[RODBC] ERROR: Could not SQLExecDirect '\nSET DATEFORMAT dmy; \ngo\nDECLARE @VerifyClientID TABLE (firstname varchar(100), middlename varchar(100), lastname varchar(100)~

The script:


SET DATEFORMAT dmy;  
go
DECLARE @VerifyClientID TABLE (firstname varchar(100), middlename varchar(100), lastname varchar(100), dob date, mobile varchar(100), ID int)
INSERT INTO @VerifyClientID (firstname, lastname,  mobile, ID)
VALUES
('JOHN','DOE','0444 444 444',1)
drop table if exists #clientTABLE
select                     v.ID, p.ABC_NCMID, P.ABC_RowID, P.ABC_FirstName, P.ABC_GuardianName, P.ABC_LastName, P.ABC_SexCode_ID, P.ABC_CellPhone, P.ABC_DOB, ABC_StreetAddress, ABC_City, ABC_Zip, ABC_SSN, ABC_HomePhone
into                       #clientTABLE
from @VerifyClientID  V
inner join dbo.DV_Person P on soundex(V.firstname) = soundex(P.ABC_FirstName)
                                                and soundex(V.lastname) = soundex(P.ABC_LastName) 
                                                and (convert(varchar,replace(replace(P.ABC_CELLPHONE,' ',''),0,'')) = convert(varchar,replace(replace(V.mobile,' ',''),0,''))
                                                       or convert(varchar,replace(replace(P.ABC_HomePhone,' ',''),0,'')) = convert(varchar,replace(replace(V.mobile,' ',''),0,''))
                                                       )
where                      1=1
and                               p.ABC_NCMID in (select Per.PER_CLIENTID from AtlasPublic.View_UODS_Person Per)
and                               P.ABC_IsPatient = 1


select                     distinct ID
                                  ,firstname
                                  ,middlename
                                  ,lastname
                                  ,dob
                                  ,mobile       
                                  , (SELECT TOP 1 ABC_NCMID FROM #clientTABLE WHERE id = v.id) as MatchedID
                                  , (SELECT TOP 1 convert(varchar, ABC_DOB, 103) FROM #clientTABLE WHERE id = v.id) as MatchedDOB
                                  , (SELECT TOP 1 case when ABC_SexCode_ID = 8089 then 'Male'
                                                                     when ABC_SexCode_ID = 8088 then 'Female'
                                                                     else null end FROM #clientTABLE WHERE id = v.id) as MatchedSEX
                                  , (SELECT TOP 1 ABC_StreetAddress FROM #clientTABLE WHERE id = v.id) as MatchedStreet
                                  , (SELECT TOP 1 ABC_City FROM #clientTABLE WHERE id = v.id) as MatchedCity
                                  , (SELECT TOP 1 ABC_Zip FROM #clientTABLE WHERE id = v.id) as MatchedZip
from @VerifyClientID  V
Banji
  • 28
  • 4
  • Have you tried removing `GO` from your script? `GO` is not a T-SQL keyword, it's a Utility Statement for SSMS and SQLCMD. – AlwaysLearning Aug 26 '21 at 06:37
  • Does this answer your question? [How to read the contents of an .sql file into an R script to run a query?](https://stackoverflow.com/questions/44853322/how-to-read-the-contents-of-an-sql-file-into-an-r-script-to-run-a-query) – ismirsehregal Aug 26 '21 at 08:10
  • @AlwaysLearning: Yes, I tried that, but didn't work out. I think the issue is the "@" symbol that needs to be escaped. – Banji Aug 30 '21 at 02:59
  • @ismirsehregal: Thanks for sharing. I read the article; didn't fix the error. I think the issue is the "@" symbol that needs to be escaped. – Banji Aug 30 '21 at 03:00
  • I didn't think `@` was a metacharacter in R, but if you think that's the problem then try replacing the `@` characters with `\u0040` and see what happens. If that still doesn't work try posting your actual R code so that people can try to reproduce the issue and suggest fixes for you. – AlwaysLearning Aug 30 '21 at 03:51
  • 1
    I was able to set it to work. I had to include "set nocount on;" at the top of the SQL script, alongside the function described in https://stackoverflow.com/questions/44853322/how-to-read-the-contents-of-an-sql-file-into-an-r-script-to-run-a-query. Thank you for your contributions – Banji Sep 07 '21 at 02:53

0 Answers0