I have a rather simple question.
On a daily basis, I perform data analysis in R using the RODBC package. I connect it to our data warehouse using SQL and move it into the R environment
dbhandle <- odbcDriverConnect('driver={SQL Server};server=SQLSERVER;database=MYDATABASE;trusted_connection=true')
degrees <- sqlQuery(dbhandle, "select Inst, ID, DegreeDate, Degree from DEGREE where FY = ('2015') group by Inst, ID, DegreeDate, Degree order by Inst, ID, DegreeDate, Degree", as.is=TRUE)
You know how in MS Access, you can have a window pop up that asks you what FY for example? You put in 2015 and you'll get all the degress from that fiscal year.
Is there any way to do it in R? The parameter query questions I see on Stack Overflow deal with changing the data in the SQL database and I'm not interested in that. I just want to set some pretty basic limits so I can rerun code.
Some may wonder "why can't you just change the 5 to a 6?" That's a fair point but I'm concerned that, with more complicated queries, users may miss a part in the SQL query to change the 5 to a 6 and that would mess the analysis up or slow it down.
Thank you! Walker
You can create a input variable at the start and pass it to your query. For example:
# Change your FY here
input_FY <- 2016
dbhandle <- odbcDriverConnect('driver={SQL Server};server=SQLSERVER;database=MYDATABASE;trusted_connection=true')
degrees <- sqlQuery(dbhandle, paste0("
select Inst, ID, DegreeDate, Degree
from DEGREE
where FY = ('", input_FY, "')
group by Inst, ID, DegreeDate, Degree
order by Inst, ID, DegreeDate, Degree"),
as.is=TRUE)
So for any complicated queries you can still pass the same input_FY variable or any other variable that you have declared at the start of code for a quick/easy update.