Prince Modi Prince Modi - 27 days ago 7
R Question

Query returning 0 rows with sqldf package

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 w02c05s02@CL.CC_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.

Answer

"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 w02c05s02@CL.CC_10",
                   systemid = 49,
                   periodicityid = 37,
                   rptday = as.Date("2014-11-04"),
                   dailynetprofit = -620)