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

Please provide reproducible code and data in your questions. — G. Grothendieck, Sep 24 '14 at 21:06
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")` — G. Grothendieck, Sep 25 '14 at 01:24

score 10 · Answer 1 · answered Apr 02 '15 at 23:43

10

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.

answered Apr 02 '15 at 23:43

winampman

484
5
15

3

I also used `difftime` on the affected dataset, there must be something to that – Hack-R Jun 02 '15 at 15:07
3

Same problem for me. I had one difftime column in my dataframe. The error went away after coercing it to numeric. – AffableAmbler Sep 27 '17 at 15:40

Young · Answer 2 · 2016-09-27T08:29:02.640

5

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.

edited Sep 27 '16 at 08:29

answered Sep 27 '16 at 08:05

Young

61
1
4

Excellent solution. Solved my problem (similar problem with the smbinning package). Thank you! – Borislav Aymaliev Dec 04 '17 at 14:44
@Young: it works fine, thank you for your answer. How do we convert the date back to its original format? – Toros91 Dec 14 '17 at 08:57

score 2 · Answer 3 · answered Nov 07 '14 at 10:30

2

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")

answered Nov 07 '14 at 10:30

peter

93
6

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 – Hack-R Jun 02 '15 at 15:06
@peter so weird I tried many things but simply listing the columns in the select statement makes it work – Jorge Feb 23 '22 at 03:49

score 1 · Answer 4 · answered Feb 24 '15 at 17:22

1

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.

answered Feb 24 '15 at 17:22

Yukun

315
4
15

This seemed to take of the problem for me. It was the only change I made and the error went away. – Jazzmine Mar 27 '16 at 00:51

R- sqldf -need explicit units for numeric conversion

4 Answers4

Linked