Error: No Such Column using SQLDF

Problem

When column names include dots, we get the following error: 'No Such Column using SQLDF'.

library(sqldf)
sqldf('SELECT Species, Sepal.Length 
      FROM iris 
      WHERE Species = "virginica" LIMIT 5')

Error: no such column: Sepal.Length

Solution

We only need to write the SQL statement between single quotes, and the column names including dots between double quotes or backticks/backquotes interchangeably.

sqldf('SELECT Species, "Sepal.Length" 
      FROM iris 
      WHERE Species = "virginica" LIMIT 5')
sqldf('SELECT Species, `Sepal.Length` 
      FROM iris 
      WHERE Species = "virginica" LIMIT 5')

    Species Sepal.Length
1 virginica          6.3
2 virginica          5.8
3 virginica          7.1
4 virginica          6.3
5 virginica          6.5

Notes

Previously we had to replace dots for underscores. This is no longer needed:

Staring with RSQLite 1.0.0 and sqldf 0.4-9 dots in column names are no longer translated to underscores.
If you are using an older version of these packages then note that since dot is an SQL operator the RSQLite driver package converts dots to underscores so that SQL statements can reference such columns unquoted.

References

• sqldf
• Error: No Such Column using SQLDF
• sqldf manual