Search code examples
roracle-databaserodbc

R writing a function to extract data from an Oracle database

I'm trying to write a function in R, which takes an input and adds it to a pre-defined SQL query. This is to avoid rewriting the same query every time the input changes, and using the function instead.

But I am having problems with the syntax of the function.

The Function

The function takes as input a unique ID, and returns a site name(s).

library(RODBC)

con= odbcConnect(dsn = "DB", uid="morp101", pwd="abcdxyz1234",rows_at_time=500)

getsitename=function(input) {

sitename=sqlQuery(con,"Select DISTINCT(SITE_NAME) FROM SITE_TABLE 
                    WHERE SITE_CODE = '&input&'")
return(sitename)

}

The above function should give the following output when tested

getsitename(1011APQ)

Result: Madison Bay

However the syntax is wrong, not sure how to concatenate the input correctly.

Any advice would be highly appreciated. And apologies for lack of reproducible data, was not sure how I could obtain it for this question.

Solution

  • You can use paste0() to create your query:

    getsitename=function(input) {
  query = paste0("Select DISTINCT(SITE_NAME) FROM SITE_TABLE WHERE SITE_CODE = '", input, "'")
  sitename=sqlQuery(con,query)
  return(sitename)
}

    So when input='1234', the paste0() statement returns 

    "Select DISTINCT(SITE_NAME) FROM SITE_TABLE WHERE SITE_CODE = '1234'"