0
$\begingroup$

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")
Improve this question
$\endgroup$

1 Answer 1

Reset to default
0
$\begingroup$

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.

Improve this answer
$\endgroup$
1
  • $\begingroup$ That makes sense, so I will start and end the select statement with "" but cannot use it for variables within the statement. I adjusted that, however, now I get this error, any ideas what is causing this? Error in postgresqlExecStatement(conn, statement, ...) : RS-DBI driver: (could not Retrieve the result : ERROR: canceling statement due to conflict with recovery DETAIL: User query might have needed to see row versions that must be removed. $\endgroup$ Commented May 25, 2020 at 10:57

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.