Single and double Quotes at SQLQuery connected to Presto


#1

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 :sob::sweat::confused:
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."

I Appreciate your unlimited support my freinds :slight_smile:


#2

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

```r
library(foo)
```

If you've never heard of a reprex before, you might want to start by reading the tidyverse.org help page. The reprex dos and don'ts are also useful.

For pointers specific to the community site, check out the reprex FAQ, linked to below.


#3
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?\"


#4

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.


#6

Interesting, does this or a similar operation fails with using dplyr?


#7

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 :slight_smile:
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 :slight_smile: thnking also to transfer my tableau work to it :slight_smile:
Have a nice day :slight_smile:


#8

@nutterb
Very nice , it works now , thanks a milion :slight_smile:


#9

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"