R- sqldf -need explicit units for numeric conversion

Question

I need to join 2 tables using a Date field

> class(pagos$pseudo_1mes)
[1] "Date"
>  class(pseudo_meses$pseudo_1mes)
[1] "Date"

My code is:

library(sqldf)

pagos<-sqldf("select a.*, b.mes_atras from pagos a 
        left join pseudo_meses b
      on a.pseudo_1mes=b.pseudo_1mes")

And I get the following error and no result:

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

How can I solve it? Thanks

Esquibel

Please provide reproducible code and data in your questions. – Semasiology

Also note that this produces the correct result for me and does not give an error: library(sqldf); DF1 <- DF2 <- data.frame(d = Sys.Date()); sqldf("select * from DF1 left join DF2 on DF1.d = DF2.d") – Semasiology

Answer 1

One of my variables had class = 'difftime' instead of regular numeric because I subtracted dates. SQLDF does not work with the difftime class. What happened for me was that I had a data.table, subtracted some dates, and ended up with a difftime variable type. I converted to it to data.frame and when I did the subtraction again it was numeric.

Lipoprotein

I also used difftime on the affected dataset, there must be something to that – Argus

Same problem for me. I had one difftime column in my dataframe. The error went away after coercing it to numeric. – Dishonor

Answer 2

I got the same problem. My solution is to add (method = "name__class") to the end of the command, such as:

sqldf("select * from pagos", method = "name__class")

However, it seem the format of date variable will be changed, albeit the date variable can be updated later. I think this is still a useful solution, since my purpose is to get sqldf work to solve a complex task while update a column is relatively much easier.

Hypogynous

Excellent solution. Solved my problem (similar problem with the smbinning package). Thank you! – Wentz

@Young: it works fine, thank you for your answer. How do we convert the date back to its original format? – Sextet

Answer 3

I got the same error message. I don't know the cause of the message, but giving explicit units in the sql query solved the problem for me.

Use:

sqldf("select x, y, z from pagos")

Don't use:

sqldf("select * from pagos")

Ermine

I got Error in asfn(rs[[i]]) : need explicit units for numeric conversion In addition: Warning message: In FUN(X[[i]], ...) : NAs introduced by coercion even with explicit column names – Argus

@Ermine so weird I tried many things but simply listing the columns in the select statement makes it work – Kilk

Answer 4

I got the same error, then I found a variable in my dataset was not numerical. So I converted that variable to numerical (as.numeric()). Then it worked.

Wardwarde

This seemed to take of the problem for me. It was the only change I made and the error went away. – Sarsenet