CODEWITHSUNDEEP
r
user8491385
user8491385

Reputation: 443

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 ".

Upvotes: 0

Views: 686

Answers (2)

RAFAEL ANTONIO VENTURA
RAFAEL ANTONIO VENTURA

Reputation: 53

I recommended a solution to a similar question. see Pasting Values in SQL Query through R

You could try defining a character variable with the query value. Then you can call function InsertListInQuery passing query and vector as it arguments as follow:

UKWinnersID<-c("'1WKX6'", "'ULTY8'", "'JNZX0'", "'8J4D8'", 
               "'KZJAJ0'", "'W8BH47'", "'CP8RPW9'", "'52TD5'", "'TLKV4'")

query <- "SELECT TOP 10000 [AxiomaDate]
                      ,[RiskModelID] ,[AxiomaID],[Factor1],[Factor2],[Factor3]
                      FROM [PortfolioAnalytics].[Data_Axioma]
                     Where  AxiomaID IN ()"

SQLQuery <- InsertListInQuery(query, UKWinnersID)

SQLQuery
[1] "SELECT TOP 10000 [AxiomaDate]\n,[RiskModelID] ,[AxiomaID],[Factor1],[Factor2],[Factor3]\nFROM [PortfolioAnalytics].[Data_Axioma]\nWhere  AxiomaID IN ( '1WKX6' , 'ULTY8' , 'JNZX0' , '8J4D8' , 'KZJAJ0' , 'W8BH47' , 'CP8RPW9' , '52TD5' ,'TLKV4')"

I hope it helps.

Upvotes: 0

Benjamin
Benjamin

Reputation: 17369

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 = ","),")"))

Upvotes: 1

Related Questions