R Question
Pasting data to SQL Query
This is driving me pretty nuts but cannot get it to work.
I want to paste the following into a SQL query within R.
UKWinnersID<-c("'1WKX6'", "'ULTY8'", "'JNZX0'", "'8J4D8'",
"'KZJAJ0'", "'W8BH47'", "'CP8RPW9'", "'52TD5'", "'TLKV4'")
sqlQuery(myConn, paste("SELECT TOP 10000 [AxiomaDate]
,[RiskModelID] ,[AxiomaID],[Factor1],[Factor2],[Factor3]
FROM [PortfolioAnalytics].[Data_Axioma]
Where AxiomaID IN (",(paste(UKWinnersID, collapse = ","),")")))
I am using the paste function now which doesn't seem to work. Anyone got an idea? The input for the last line would be like this (with the data stored in a) for the query to work..
AxiomaID IN ('1WKX6', 'ULTY8', 'JNZX0', '8J4D8',
'KZJAJ0', 'W8BH47', 'CP8RPW9', '52TD5', 'TLKV4')
The current output is
sqlQuery etc Where AxiomaID IN ( '1WKX6','ULTY8','JNZX0','8J4D8','KZJAJ0','W8BH47','CP8RPW9','52TD5','TLKV4' )'"
Essentially I want to remove the last two characters in this paste which is a ' and ".
Recommended for you: Get network issues from WhatsUp Gold. Not end users.
Answer Source
I'm not able to recreate your exact output. Try the following; it produces the SQL code I would expect.
UKWinnersID<-c("'1WKX6'", "'ULTY8'", "'JNZX0'", "'8J4D8'",
"'KZJAJ0'", "'W8BH47'", "'CP8RPW9'", "'52TD5'", "'TLKV4'")
sqlQuery(myConn, paste("SELECT TOP 10000 [AxiomaDate]
,[RiskModelID] ,[AxiomaID],[Factor1],[Factor2],[Factor3]
FROM [PortfolioAnalytics].[Data_Axioma]
Where AxiomaID IN (",paste(UKWinnersID, collapse = ","),")"))
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download