Running a query in R after establishing dbconnect

I do not seem to figure out what is wrong it the following statement. The connection to the DWH is established but the query statement in R seems not to work, with the following error :

LR=dbGetQuery(con, select id as ID,
       date_c.Professional_Status as Prof_Status,
       case when talk_sec = 5 then 1 else 0 end as Established_Connection
from id_collect as id_c
left join date_conncet as date_c on id_c.date=date_c.date
where date::date = '2018-01-19' and country = 'IT' and type = 'shop' 
              and 'district' = 'South' and interaction is true)

This piece of query runs in the SQL editor (using Postgresql) but in R I get the error message: Error: unexpected symbol in or unexpected numeric constant. I do use the following:

install.packages(RPostgreSQL)
install.packages(DBI)
library(RPostgreSQL)

I believe it is not about the syntax but the quote I use, what's the proper quote etiquette in R when using numeric vs. categorical values? The whole select statement must begin and end with select .... correct?

Also to add how to establish a connection to your DTW (add your DWH info):

con - dbConnect(RPostgreSQL::PostgreSQL(),
    dbname=DBNAME,
    host= HOSTNAME,
    port=0000,
    user=youruser,
    password=yourpassword)

Topic sql data-formats r

Category Data Science

1
answered at

I understand your question as: I try to send this query to the database but it doesn't work. As far as I can see the trouble is here:

"select id as ID,
       date_c."Professional_Status" as

You first start with double quotes and R thinks that the string ends at the next double quotes, so before Professional_Status. Use either single quotes like you do in date = '2018-01-19' Or use single quotes at the ends and double in between. Don't mix and match, it will not work. Since Profesional status is not defined variable nor is it a valid R statement this will fail.

Some small tips: If you want to substitute professional_status with a variable you have defined in your R session, use something like paste("text", variable " text") or use the excellent glue package that allows you to write glue("text {variable} text") A final tip, if you use an IDE, like for instance RStudio, you can actually see that the text ends at the first second quote, because the highlighting is different.

About

Geeks Mental is a community that publishes articles and tutorials about Web, Android, Data Science, new techniques and Linux security.