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]]


