2

I would like to take the values from a data frame and paste them into a text string that can be used as a sql query. In SAS I would do it 

proc sql noprint; Select Names into :names separated by ", " from df; quit;

this would create a variable &name storing all the names. Like: Id, Name, Account. I would like to do this same type of thing in R, but do not know how. I can create a vector with names separated by comma and each one is surrounded by quotes, I can take away the quotes using noquote function and have them in a vector, but I can not get the elements in another paste statement to add the "SELECT" and FROM. I can not get it to all paste. Is there a way to pull the values on Column and create a text string that can be used as a SQL query inside R? Here is what I have tried in R:

name = c("Id", "IsDeleted", "Name", "Credit__Loan__c")
label = c("Record Id", "Deleted", "ID", "Loan")
df = data.frame(name, label)
names(df) <- c("name", "label")

as.query.fields = noquote(paste(df$name, collaspe=", "))
as.query.final <- paste("SELECT " , noquote(paste(df$name, collaspe=", ")), " id FROM Credit_Amortization_Schedule__c")
Joe
  • 62,789
  • 6
  • 49
  • 67
muraenok
  • 95
  • 1
  • 9
  • I think the `whisker` or `brew` packages will be helpful here. – ctbrown Oct 29 '14 at 20:53
  • Don't know if you have realized, but you have `collapse` spelled incorrectly (you have `collaspe`), and this affects the final result. You don't need the `noquote` function (it only affects the way objects are printed) and you can pass the `as.query.final` object directly in the call of `sqlQuery` (or similar functions) to execute the query. – nicola Oct 29 '14 at 21:51
  • Yes thanks for catching the spelling. That was just a typo and not the issue. Also, I am using R to query Salesforce (using rforcecom and soql) so I really need to control the creation of the Select statement, so the sqlQuery from as.query.final does not work. The only thing I can do is use the cat method to screen and copy and paste the result and then remove the trailing comma before the from statement. Alas in my laziness I was hoping to be able to fully automate it but I can not. thanks – muraenok Oct 29 '14 at 23:16

2 Answers2

2
data(iris)
colnames(iris)
a <- noquote(paste(colnames(iris), collaspe=", "))
as.query.final <- cat("SELECT " , a, " id FROM Credit_Amortization_Schedule__c")

The result is:

SELECT  Sepal.Length ,  Sepal.Width ,  Petal.Length ,  Petal.Width ,  Species ,   id FROM Credit_Amortization_Schedule__c

which you can then use with SQL like this:

require(RODBC)
result <- sqlQuery(db, as.query.final)

where db is your database connection

Or, since I see your sqldf tag now, if you want to use sqldf it's just:

sqldf(as.query.final)
Hack-R
  • 22,422
  • 14
  • 75
  • 131
  • I see that your method prints it to the screen and works, but I do not get it to store the value of the cat into vector call as.query.final. When I print that vector it is NULL. Did I miss something – muraenok Oct 29 '14 at 21:09
  • Oh, okay hmmm I tested it with `sqlQuery` but maybe you're using it differently or something? If you can paste the error/problem you're describing then I will help you fix it – Hack-R Oct 30 '14 at 13:18
0

The gsubfn package supports string interpolation:

library(gsubfn)

Names <- toString( sprintf("%s '%s'", df$name, df$label) )
fn$identity("select $Names from myTable")

giving:

[1] "select Id 'Record Id', IsDeleted 'Deleted', Name 'ID', Credit__Loan__c 'Loan' from myTable"

Here some additional examples: SO example 1 and SO example 2 .

Community
  • 1
  • 1
G. Grothendieck
  • 254,981
  • 17
  • 203
  • 341
  • This works great. Thanks this is just what I was looking to do. It even drops the last comma automatically. Many thanks... I am using the query in a non standard sub query language so I needed to control precisely what is the output. – muraenok Oct 30 '14 at 15:54
  • I am trying to find the documentation for fn$identity. so I can figure out how to use it better. I did not see it on the CRAN pdf? Did I miss something? The possibilities this opens for my code, I would like to know more. I am looking through the package and wow! thanks... – muraenok Oct 30 '14 at 16:13
  • Try: `library(gsubfn); ?fn` Also `vignette(gsubfn)`. The home page http://gsubfn.googlecode.com has some info too. – G. Grothendieck Oct 30 '14 at 19:50