sqldf in R generating an Error

Question

I am trying to merge two tables in R by using the following command:

library(gsubfn)
library(proto)
library(RSQLite)
library(sqldf)
sqldf("SELECT Gender, Pmu.try.Sat.Ltd, Online.Campus.Student, Residency,
           tutorial_avg_score
           FROM A JOIN B
           USING(userID)")

The system generates the following error:

Error in result_create(conn@ptr, statement) : near ".": syntax error

Could anyone kindly advise what could be the issue here? The column names are bit weird but they are very similar to the actual names that I have in the provided data. I had a look at the following question: Error in rsqlite_send_query(conn@ptr, statement) : near "(": syntax error But it does not help me either.

Looking for your kind advice on this!

Dot is an SQL operator so if you have columns with a dot in their name they must be surrounded with double quotes "..." or with square brackets [...] — G. Grothendieck, Aug 06 '18 at 23:43
Thanks that helped, but "..." did not work. Instead the `` underneath tilde sign did the magic. The square brackets also work fine, I was not aware of it, thanks again! — Sandy, Aug 06 '18 at 23:46
Double quotes do work. Maybe you tried to use double quotes within double quotes. You have to use single quotes on the outside if you do that or else use backslash to escape them. — G. Grothendieck, Aug 06 '18 at 23:58

score 1 · Accepted Answer · answered Aug 11 '18 at 07:46

The following solutions resolved the error that I had been facing:

sqldf('SELECT Gender, "Pmu.try.Sat.Ltd", "Online.Campus.Student", Residency,
       tutorial_avg_score
       FROM A JOIN B
       USING(userID)')

OR

sqldf('SELECT Gender, `Pmu.try.Sat.Ltd`, `Online.Campus.Student`, Residency,
       tutorial_avg_score
       FROM A JOIN B
       USING(userID)')

OR

sqldf('SELECT Gender, [Pmu.try.Sat.Ltd], [Online.Campus.Student], Residency,
       tutorial_avg_score
       FROM A JOIN B
       USING(userID)')

Many thanks @G. Grothendieck for your kind comments!

sqldf in R generating an Error

1 Answers1