Error in R Using SQLDF: too many SQL variables

Question

I have a large dataset with nearly 2000 variables in r. I then use sqldf to write a few case statements to create new columns on the original dataset. However I get the following error:

 Error in rsqlite_send_query(conn@ptr, statement) : too many SQL variables

I rebooted my laptop today and previously this error never occured.

Any help is appreciated.

Could you clarify on what I should input in r? The link gives info but I can't adjust the maximum columns. I've tried "SQLITE_MAX_COLUMN" with no avail — greeny
– greeny, Commented Jan 12, 2017 at 6:45
There is nothing you can do in R. You would need to get the source to RSQLite from CRAN or github, change SQLITE_MAX_COLUMN in the SQLite C code included with RSQLite and then rebuild the package -- which will also rebuild SQLite. — G. Grothendieck
– G. Grothendieck, Commented Jan 12, 2017 at 13:38
Ok so I've got the package source for RSQLite from CRAN. I think I've changed the correct part of the package in the 'sqlite3' folder within 'src'. I then compressed the edited folder as a new .gz file. I then entered this into terminal "R CMD INSTALL path/RSQLite_1.1-3.tar.gz" and reinstalled the package in r. But still no luck. Could you elaborate further? — greeny
– greeny, Commented Jan 14, 2017 at 7:48
Not an answer to your question, but: whatever you do with sqldf you can do in base R or with the tidyverse or data.table, with less limitations. — Aurèle
– Aurèle, Commented Jan 20, 2017 at 16:14

MatthewR · Accepted Answer · 2017-10-19 18:04:24Z

1

I hit the same problem. I just limited the number of columns

# here creating data with alot of columns
a<- mtcars
for( i in 1:1000 ){
b <- mtcars
colnames(b) <- paste( colnames(b), i , sep="_")
a <- cbind( b , a )
}

ncol( a )

# I get the error here
sqldf( "Select SUM( wt) as weights from a ")

#so I just limited the columns
z <- a[ , c( "gear","wt")]
# and than this works
sqldf( "Select SUM( wt ) as weights from z ")

answered Oct 19, 2017 at 18:04

MatthewR

2,7806 gold badges29 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error in R Using SQLDF: too many SQL variables

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related