Hi my dear friends,
Usually when i face a problem i search before asking here, but i'm facing a problem where i couldn't find a solution for it by search.
i'm using Connection to a database at Presto,
Connection has been done successfully but the issue is in writing a query with Single and double Quotes , Please help me in these 10's of errors
the code used is ;
library(DBI)
library(dplyr)
library(RPresto)
res = dbSendQuery(con, " select RESPONDENT as Respondent,RESPONSE_DATE as Date,
MAX(CASE
WHEN QUESTION_ID = '18' AND RESPONSE_ID ='5' THEN 'Very Satisfied'
WHEN QUESTION_ID = '18' AND RESPONSE_ID ='4' THEN 'Satisfied'
WHEN QUESTION_ID = '18' AND RESPONSE_ID ='3' THEN 'Neutral'
WHEN QUESTION_ID = '18' AND RESPONSE_ID ='2' THEN 'Dissatisfied'
WHEN QUESTION_ID = '18' AND RESPONSE_ID ='1' THEN 'Very Dissatisfied' ELSE NULL END) AS "How was our service looks based on your last meal?"
from surveytable
group by 1,2")
Sample from Errors appeared Error: unexpected numeric constant in: " WHEN QUESTION_ID = '18' AND RESPONSE_ID ='2' THEN 'Dissatisfied' **
** WHEN QUESTION_ID = '18' AND RESPONSE_ID ='1' THEN 'Very Dissatisfied' ELSE NULL END) AS "7."
Could you please turn this into a self-contained reprex (short for minimal reproducible example)? It will help us help you if we can be sure we're all working with/looking at the same stuff.
If you can't create a reprex because of the database connection or something to that effect, it would be great if you could add formatting to your post just to make it more legible. To do so, you just add triple backticks around the code chunk (and add an R next to the opening backticks for syntax highlighting):
res = dbSendQuery(con, " select RESPONDENT as Respondent,RESPONSE_DATE as Date,
MAX(CASE
WHEN QUESTION_ID = '18' AND RESPONSE_ID ='5' THEN 'Very Satisfied'
WHEN QUESTION_ID = '18' AND RESPONSE_ID ='4' THEN 'Satisfied'
WHEN QUESTION_ID = '18' AND RESPONSE_ID ='3' THEN 'Neutral'
WHEN QUESTION_ID = '18' AND RESPONSE_ID ='2' THEN 'Dissatisfied'
WHEN QUESTION_ID = '18' AND RESPONSE_ID ='1' THEN 'Very Dissatisfied'
ELSE NULL END) AS "How was our service looks based on your last meal?"
from surveytable
group by 1,2")
Putting your code into a code-formatted environment shows that after the AS at the end of your case statement, you've terminated your R string. I wouldn't expect this is ever making it to the database because R can't resolve what string your trying to send. You need to add a backslash in front of any double quotes you wish to send to the SQL database, such as
ELSE NULL END) AS \"How was our service looks based on your last meal?\"
Hi @hady, I see that you're loading the dplyr package, but are sending a regular DBI command. Not sure if this will eventually be re-written using dbplyr
btw - can you try running that query in Presto? I don't see how a DB could select a MAX from just discrete, text data. maybe the THENs need to be a number.
fortunately it doesn't fail because a friend here advised me to write a backslash before each double quotation in the Query and it works
Thanks for your support @edgararuiz. please bear with me that i'm a new beginner in R environment. But i adore it and try to transfer all my excel tasks to it thnking also to transfer my tableau work to it
Have a nice day
I just tried something like this on SQL Server. It turns out that taking the MAX of a VARCHAR is a thing. It sorts the column and takes the last value (I guess the greatest by alphabetical order).
Whether that is what @hady wants is a good question. For instance, if a selection turns up values of "Neutral" and "Very Dissatisfied", the end result will be "Very Dissatisfied," which doesn't feel like a larger value than "Neutral"
