I'm trying to pull data from multiple MS SQL Sever databse in R. I can access
the databases one at a time, but the tables are to large to pull the entire
tables then join then in R. So I need to do a SQL join that will join the
tables from the each of the databases. How do I combine the connection so that
I can implement it in my sql query below. I'm currently getting the following
error:
region
[1] "[RODBC] ERROR: Could not SQLExecDirect"
[2] "42000 102 [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax
near 'Completed'."
When I look at the tables from the contab<-sqlTables(con), it is only getting
information from one of the databases.
library(RODBC)
dwparadigm<-odbcConnect(dsn="dwParadigm", uid = "XXXXX", pwd = "XXXXXX", case =
"nochange", believeNRows = TRUE)
rptparadigm<-odbcConnect(dsn="Rpt_Paradigm", uid = "XXXXX", pwd = "XXXX", case
= "nochange", believeNRows = TRUE)
wcrpt<-odbcConnect(dsn="WC_Reporting", uid = "XXXXX", pwd = "XXXXX", case =
"nochange", believeNRows = TRUE)
con<-odbcConnect(dsn=c("dwParadigm","Rpt_Paradigm","WC_Reporting")
contab<-sqlTables(con)
region<-sqlQuery(con,'SELECT DISTINCT dbo_PATIENT.PATIENT,
dbo_CONTRACT.CONTRACT, dbo_PATIENT.PATIENT_EPISODE, dbo_PATIENT.LAST_NAME,
dbo_PATIENT.FIRST_NAME, dbo_CONTRACT.CON_STATUS, dbo_CONTRACT.CON_NUMBER,
dbo_PATIENT.STATE, dbo_REGION_TERR.OPS_REGION, dbo_REGION_TERR.SPG_TERR,
Sum(dbo_Line.total_ln_paid_amount) AS SumOftotal_ln_paid_amount
FROM dbo_CONTRACT
INNER JOIN dbo_REGION_TERR
RIGHT JOIN dbo_PATIENT
ON dbo_REGION_TERR.STATE = dbo_PATIENT.STATE
ON dbo_CONTRACT.PATIENT = dbo_PATIENT.PATIENT
AND dbo_CONTRACT.PatientEpisodeID =
dbo_PATIENT.PATIENT_EPISODE
INNER JOIN dbo_Line
ON dbo_PATIENT.PATIENT = dbo_Line.claim
WHERE dbo_PATIENT.DIAGNOSIS_TYPE="ABI"
AND dbo_CONTRACT.CON_STATUS In "Completed"
AND dbo_CONTRACT.CON_NUMBER Like "OP*"
GROUP BY dbo_PATIENT.PATIENT, dbo_CONTRACT.CONTRACT,
dbo_PATIENT.PATIENT_EPISODE, dbo_PATIENT.LAST_NAME
, dbo_PATIENT.FIRST_NAME,
dbo_CONTRACT.CON_STATUS, dbo_CONTRACT.CON_NUMBER, dbo_PATIENT.STATE
, dbo_REGION_TERR.OPS_REGION,
dbo_REGION_TERR.SPG_TERR
')
odbcCloseAll()
region
[1] "[RODBC] ERROR: Could not SQLExecDirect"
[2] "42000 102 [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax
near 'Completed'."
>
Chris Anderson
http://www.seocodebreaker.com/?thankyou-page=429
____________________________________________________________
The easiest way to create a blog. Click now!
http://thirdpartyoffers.netzero.net/TGL2241/fc/BLSrjpYRxiYGYCuyKabTb5JapHgbWqUjvZ4P8AguC0ACizbuKJCNdYf7SL2/
[[alternative HTML version deleted]]
______________________________________________
[email protected] 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.
