Issue in running sqldf command for comparing integers

Question

I am working on simple data like below:

teacher student
  12      409
  43      403
  12      415
  12      409
  67      311
  19      201

I am trying to retrieve the entries where teacher = 12 and student = 409. I am using the following command:

library(sqldf)
sqldf('SELECT * 
      FROM df
      WHERE teacher == 12 and student == 409')

I know it is a basic command, but when I run it, I get the following error message:

Error in asfn(rs[[i]]) : need explicit units for numeric conversion

I get the same error, even when I run:

# Without the and condition
sqldf('SELECT * 
      FROM df
      WHERE teacher == 12')

or when I run this

# Single equal sign
sqldf('SELECT * 
      FROM df
      WHERE teacher = 12')

Please note that in my current dataset df$teacher and df$student are both integers. I want to understand why am I getting this error. Any advice would be greatly appreciated.

My desired output is:

teacher   student
  12        409
  12        409

Thanks!

Relevant - https://stackoverflow.com/questions/26025849/r-sqldf-need-explicit-units-for-numeric-conversion — thelatemail, Jun 03 '21 at 00:48

akrun · Accepted Answer · 2021-06-03T00:59:59.863

1

We just need single =

library(sqldf)
sqldf('SELECT * 
      FROM df
      WHERE teacher = 12 and student = 409')

-output

 teacher student
1      12     409
2      12     409

With the OP's original data, that didn't fix, so, we may use the method argument`

sqldf('SELECT * 
      FROM df
      WHERE teacher = 12 and student = 409', method = 'name__class')

According to the ?sqldf, the "name__class" is used with Date class as an example. As the OP's original data is not shared, it is still not sure why it worked

method = "name__class" which means that columns names that end in __class with two underscores where class is an R class (such as Date) are converted to that class and the __class portion is removed from the column name.`

data

df <- structure(list(teacher = c(12L, 43L, 12L, 12L, 67L, 19L), student = c(409L, 
403L, 415L, 409L, 311L, 201L)), class = "data.frame", row.names = c(NA, 
-6L))

edited Jun 03 '21 at 00:59

answered Jun 03 '21 at 00:35

akrun

874,273
37
540
662

Thank you, but I still get the same error even with a single ```=``` sign. – Sandy Jun 03 '21 at 00:37
this is it ```Error in asfn(rs[[i]]) : need explicit units for numeric conversion``` – Sandy Jun 03 '21 at 00:38
@Sandy, I couldn't reproudce your error though – akrun Jun 03 '21 at 00:41
@Sandy I also tested by changing the class of the columns from `integer` to `numeric`. it still works for me though. Can you show `packageVersion('sqldf')`. I used `0.4.11` – akrun Jun 03 '21 at 00:44
It is running on the sample data. But unfortunately, not on my original data with ```dim = 15994 * 34``` – Sandy Jun 03 '21 at 00:45
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/233258/discussion-between-sandy-and-akrun). – Sandy Jun 03 '21 at 00:45
@Sandy It may be that your columns have precision i.e. it may be exactly 409 i..e `409.00000013` etc – akrun Jun 03 '21 at 00:46

Issue in running sqldf command for comparing integers

1 Answers1

data