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")