Query returning 0 rows with sqldf package

Question

I am trying to retrieve some rows from my data frame using sqldf package but the query is returning 0 rows despite the data being there.

str(DATA)
'data.frame':   51500 obs. of  5 variables:
$ MaxBullProb   : Factor w/ 100 levels "CX000096 [email protected]_10",..: 1 2 3 4 5 6 7 8 9 10 ...
$ systemid      : int  49 49 49 49 49 49 49 49 49 49 ...
$ periodicityid : int  37 48 58 43 52 45 4 56 80 40 ...
$ rptday        : Date, format: "2014-11-03" "2014-11-03" "2014-11-03" "2014-11-03" ...
$ dailynetprofit: int  -620 -2000 -470 -2250 -1830 -1590 750 685 -315 -555 ...

The query I am running is:

QUERY<-"SELECT rptday,dailynetprofit from DATA WHERE rptday > '2014-11-03'"
QUERY_RES<-sqldf(QUERY)

QUERY_RES
[1] rptday         dailynetprofit
<0 rows> (or 0-length row.names)

I am not sure why the query is not working.

after doing dput(DATA), i saw that my dates are coming as 16084, etc instead of '2014-11-03'. So i replaced '2014-11-03' with 16084 and the query worked. — Prince Modi
– Prince Modi, Commented Nov 9, 2016 at 22:52

G. Grothendieck · Accepted Answer · 2016-11-11 16:39:09Z

3

"Date" class columns are transferred to SQLite as the number of days since the Epoch so try this. See ?fn .

library(sqldf)

compareDate <- as.Date("2014-11-03")
QUERY <- "SELECT rptday, dailynetprofit from DATA WHERE rptday > $compareDate"
fn$sqldf(QUERY)

which, for the one row test input in the Note below, gives:

      rptday dailynetprofit
1 2014-11-04           -620

Note: Test input in reproducible form is (next time please provide it like this):

DATA <- data.frame(MaxBullProb = "CX000096 [email protected]_10",
                   systemid = 49,
                   periodicityid = 37,
                   rptday = as.Date("2014-11-04"),
                   dailynetprofit = -620)

edited Nov 11, 2016 at 16:39

answered Nov 9, 2016 at 22:55

G. Grothendieck

273k18 gold badges221 silver badges365 bronze badges

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

Collectives™ on Stack Overflow

Query returning 0 rows with sqldf package

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