1
$\begingroup$

I have a list which has to pass one of it's elements to a SQL Server Query, While in R list elements will be in double quotes, SQL Server will not accept double quotes in a query as pattern. So I need to remove double quotes and add single quotes when adding to query. I have tried several solutions but failed .

i.e. categories <- c('Casual','Formal','Night-Wear','Others','Party','Sports')

str_input5 <- categories[3]

ycategoryDF1 <- sqlQuery(con,paste0("select year(sales_data.sold_date) as Year,
                                        sum(sales_data.net_pay) as Revenue 
                                FROM item_data,sales_data 
                                where sales_data.item_id = item_data.item_id and
                                item_category = ",paste(cat(str_input5)),"
                                group by  year(sales_data.sold_date) 
                                order by Year"))

it is showing NULL in place of str_input5. I need to get str_input5 value as 'Night-Wear'. There are no other errors in this: while I am trying static values i am getting accurate result.

Thanks in advance, V L Sudhakat Inampudi

$\endgroup$
  • 3
    $\begingroup$ Please note, that questions on how to use specific statistics software are not on topic on Cross Validated. I put an answer below, but please consider asking questions like this on stack overflow. Cf. stats.stackexchange.com/help/on-topic $\endgroup$ – Bernhard Jan 3 '18 at 7:28
0
$\begingroup$

Do not use cat, just put the single quotes within the paste0 function:

str_input5 <- "Night Wear"
paste0("SELECT year(sales_data.sold_date) AS Year,
                                    sum(sales_data.net_pay) AS Revenue 
                            FROM item_data,sales_data 
                            WHERE sales_data.item_id = item_data.item_id and
                            item_category = '", str_input5, "'
                            GROUP BY  year(sales_data.sold_date) 
                            ORDER BY Year")
$\endgroup$
0
$\begingroup$

Use sprintf; it's more convenient and easier to read than pasteing:

sprintf('select year(sales_data.sold_date) as Year,
       sum(sales_data.net_pay) as Revenue 
       FROM item_data,sales_data 
       where sales_data.item_id = item_data.item_id and
       item_category = "%s"
       group by  year(sales_data.sold_date) 
       order by Year', str_input5)

As its documentation says, cat only outputs its arguments, it doesn't return anything besides an invisible NULL. You can't use it for anything other than output (and I've personally never had a reason to use it).

$\endgroup$
  • $\begingroup$ Thanks @Roland, I just seen in other blog and i made a try......Thanks for the info $\endgroup$ – V L Sudhakar Inampudi Jan 3 '18 at 10:02

Not the answer you're looking for? Browse other questions tagged or ask your own question.