I think your problem is caused by DTTM in testeq being POSIXlt. This
is a strange class to have in a dataframe. Try is as POSIXct.
Example:
> str(testeq)
'data.frame': 6 obs. of 5 variables:
$ NAME : chr "DLF" "DLF" "DLF" "DLF" ...
$ TMSTMP: chr "09:07:07" "09:15:03" "09:15:03" "09:15:03" ...
$ PRICE : num 295 294 294 295 295 ...
$ DATE : Factor w/ 124 levels "2011-01-03","2011-01-04",..: 1 1 1 1 1 1
$ DTTM : POSIXlt, format: "2011-01-03 09:07:07" "2011-01-03
09:15:03" "2011-01-03 09:15:03" ...
> sqldf("select * from testeq")
Error in sqliteExecStatement(con, statement, bind.data) :
RS-DBI driver: (error in statement: no such table: testeq)
In addition: Warning message:
In value[[3L]](cond) : RAW() can only be applied to a 'raw', not a 'double'
Enter a frame number, or 0 to exit
1: sqldf("select * from testeq")
2: dbGetQuery(connection, xi)
3: dbGetQuery(connection, xi)
4: sqliteQuickSQL(conn, statement, ...)
5: sqliteExecStatement(con, statement, bind.data)
Selection: sqldf("select * from testeq")>
> testeq$DTTM <- as.POSIXct(testeq$DTTM)
> sqldf("select * from testeq") # this works
NAME TMSTMP PRICE DATE DTTM
1 DLF 09:07:07 295.0 2011-01-03 2011-01-03 14:07:07
2 DLF 09:15:03 294.5 2011-01-03 2011-01-03 14:15:03
3 DLF 09:15:03 293.9 2011-01-03 2011-01-03 14:15:03
4 DLF 09:15:03 294.9 2011-01-03 2011-01-03 14:15:03
5 DLF 09:15:03 295.0 2011-01-03 2011-01-03 14:15:03
6 DLF 09:15:04 294.5 2011-01-03 2011-01-03 14:15:04
>
On Wed, May 9, 2012 at 5:58 PM, Shivam <shivamsi...@gmail.com> wrote:
> Hi All,
>
> I am having trouble executing SQL statements on a few dataframes, but the
> funny thing is that I am able to execute the statement on some other
> dataframes.
>
> To test, I have 2 very small dataframes (6 rows and some columns). One is
> 'lessliq', the dput is given below.
>
>> dput(head(lessliq))
> structure(list(V1 = c(50464677L, 50464846L, 50432581L, 50426614L,
> 50504329L, 50504735L), V2 = c("TATASTEEL", "TATASTEEL", "TATASTEEL",
> "TATASTEEL", "TATASTEEL", "TATASTEEL"), V3 = c("OPTSTK", "OPTSTK",
> "OPTSTK", "OPTSTK", "OPTSTK", "OPTSTK"), V4 = structure(c(15029,
> 15029, 15029, 15029, 15029, 15029), class = "Date"), V5 = c("CE",
> "CE", "CE", "CE", "CE", "CE"), V6 = c(0L, 0L, 0L, 0L, 0L, 0L),
> V7 = c(700, 700, 700, 700, 700, 700), V8 = c("14:15:45",
> "14:15:51", "13:51:12", "13:45:13", "14:39:53", "14:40:08"
> ), V9 = c(37, 37, 37.75, 37, 37.5, 37.5), V10 = c(500L, 500L,
> 500L, 500L, 2000L, 500L), V11 = structure(c(14977, 14977,
> 14977, 14977, 14977, 14977), class = "Date"), V12 = c(52,
> 52, 52, 52, 52, 52)), .Names = c("V1", "V2", "V3", "V4",
> "V5", "V6", "V7", "V8", "V9", "V10", "V11", "V12"), row.names = c(NA,
> 6L), class = "data.frame")
>
> I run the below command:
>
>> new2 = sqldf("select * from lessliq")
> This works fine.
>
> But on many other dataframes it is not working. I have a dataframe
> 'testeq'. dput given below:
>
>> dput(head(testeq))
> structure(list(NAME = c("DLF", "DLF", "DLF", "DLF", "DLF", "DLF"
> ), TMSTMP = c("09:07:07", "09:15:03", "09:15:03", "09:15:03",
> "09:15:03", "09:15:04"), PRICE = c(295, 294.5, 293.9, 294.9,
> 295, 294.5), DATE = structure(c(1L, 1L, 1L, 1L, 1L, 1L), .Label =
> c("2011-01-03",
> "2011-01-04", "2011-01-05", "2011-01-06", "2011-01-07", "2011-01-10",
> "2011-01-11", "2011-01-12", "2011-01-13", "2011-01-14", "2011-01-17",
> "2011-01-18", "2011-01-19", "2011-01-20", "2011-01-21", "2011-01-24",
> "2011-01-25", "2011-01-27", "2011-01-28", "2011-01-31", "2011-02-01",
> "2011-02-02", "2011-02-03", "2011-02-04", "2011-02-07", "2011-02-08",
> "2011-02-09", "2011-02-10", "2011-02-11", "2011-02-14", "2011-02-15",
> "2011-02-16", "2011-02-17", "2011-02-18", "2011-02-21", "2011-02-22",
> "2011-02-23", "2011-02-24", "2011-02-25", "2011-02-28", "2011-03-01",
> "2011-03-03", "2011-03-04", "2011-03-07", "2011-03-08", "2011-03-09",
> "2011-03-10", "2011-03-11", "2011-03-14", "2011-03-15", "2011-03-16",
> "2011-03-17", "2011-03-18", "2011-03-21", "2011-03-22", "2011-03-23",
> "2011-03-24", "2011-03-25", "2011-03-28", "2011-03-29", "2011-03-30",
> "2011-03-31", "2011-04-01", "2011-04-04", "2011-04-05", "2011-04-06",
> "2011-04-07", "2011-04-08", "2011-04-11", "2011-04-13", "2011-04-15",
> "2011-04-18", "2011-04-19", "2011-04-20", "2011-04-21", "2011-04-25",
> "2011-04-26", "2011-04-27", "2011-04-28", "2011-04-29", "2011-05-02",
> "2011-05-03", "2011-05-04", "2011-05-05", "2011-05-06", "2011-05-09",
> "2011-05-10", "2011-05-11", "2011-05-12", "2011-05-13", "2011-05-16",
> "2011-05-17", "2011-05-18", "2011-05-19", "2011-05-20", "2011-05-23",
> "2011-05-24", "2011-05-25", "2011-05-26", "2011-05-27", "2011-05-30",
> "2011-05-31", "2011-06-01", "2011-06-02", "2011-06-03", "2011-06-06",
> "2011-06-07", "2011-06-08", "2011-06-09", "2011-06-10", "2011-06-13",
> "2011-06-14", "2011-06-15", "2011-06-16", "2011-06-17", "2011-06-20",
> "2011-06-21", "2011-06-22", "2011-06-23", "2011-06-24", "2011-06-27",
> "2011-06-28", "2011-06-29", "2011-06-30"), class = "factor"),
> DTTM = structure(list(sec = c(7, 3, 3, 3, 3, 4), min = c(7L,
> 15L, 15L, 15L, 15L, 15L), hour = c(9L, 9L, 9L, 9L, 9L, 9L
> ), mday = c(3L, 3L, 3L, 3L, 3L, 3L), mon = c(0L, 0L, 0L,
> 0L, 0L, 0L), year = c(111L, 111L, 111L, 111L, 111L, 111L),
> wday = c(1L, 1L, 1L, 1L, 1L, 1L), yday = c(2L, 2L, 2L,
> 2L, 2L, 2L), isdst = c(0L, 0L, 0L, 0L, 0L, 0L)), .Names = c("sec",
> "min", "hour", "mday", "mon", "year", "wday", "yday", "isdst"
> ), class = c("POSIXlt", "POSIXt"))), .Names = c("NAME", "TMSTMP",
> "PRICE", "DATE", "DTTM"), row.names = c(NA, 6L), class = "data.frame")
>
> I run the above command again but this time I get an error:
>
>> new3 = sqldf("select * from testeq")
> Error in sqliteExecStatement(con, statement, bind.data) :
> RS-DBI driver: (error in statement: no such table: testeq)
> In addition: Warning message:
> In value[[3L]](cond) : RAW() can only be applied to a 'raw', not a 'double'
>
> Can anyone guide me if there is any difference in the structure of the two
> dataframes or what else can be the issue?
>
> Thanks in advance,
>
> Regards,
> Shivam Singh
>
> [[alternative HTML version deleted]]
>
> ______________________________________________
> R-help@r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
--
Jim Holtman
Data Munger Guru
What is the problem that you are trying to solve?
Tell me what you want to do, not how you want to do it.
______________________________________________
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
