rmonetdblite

Problem using WHERE clause in MonetDBLite in R

I am trying to use MonetDBLite in R64bit 3.5.1. My problem is that I can not filter the data using SQL command like this example:

dbGetQuery(DB,'select * from table1 where "var1" = "1"')

I get this error:

Error in .local(conn, statement, ...) : 
  Unable to execute statement 'select * from table1  where "var1" = "1"'.
Server says 'ParseException:SQLparser:42000!SELECT: identifier '1' unknown'.

Any ideas?

Solution

  • For constant values, you need to use single quotes (') or none for numeric values. So in your example,

    dbGetQuery(DB,'select * from table1 where "var1" = 1') or

    dbGetQuery(DB,'select * from table1 where "var1" = \'1\'') or

    dbGetQuery(DB,"select * from table1 where \"var1\" = '1'")

    should all work.

    The general rule is that identifiers (table1 or var1) generally only need to be quoted with " if they contain spaces or uppercase characters and constants (1) only need to be quoted with ' if they are character strings.