Hi everyone,
I am desperately looking for a method to interpolate strings within an SQL
statement as follows:
I get a lot of rows out of a database (in my example POSITION_to_ZIPCODE
Database with holds records for German ZIP Code <--> Gauss-Krueger
Coordinate System ) and want this to be selected and computed individually
row by row as follows:
library(RODBC)
channel <- odbcConnect("database")
pos_to_zip <- sqlQuery(channel, "select YPOS, XPOS FROM
POSITION_to_ZIPCODE;")
my_row <- pos_to_zip[1,] # get the first element (change with next
ones afterwards)
ypos <-my_row[1] # get the first y-position xpos <-my_row[2] #
get the first X-Position
So far, so good: SQL Select works and everybody is fine, but this was just
the preparation for the next step: Select all data out of the database with
e.g. ypos = 1 and xpos = 182...
rawdata <- sqlQuery(channel, "select YPOS, XPOS, YEAR, MONTH, RANGE, APFEL,
TEMPMIN, TEMPAVG, TEMPMAX, PRECIPITATION, SUNDURATION from DATA where
YPOS='ypos' AND XPOS='xpos' AND MONTH BETWEEN 1 AND 12 AND RANGE = 1;")
The problem is that I want to use variables (xpos, ypos) to be replaced by
the numbers (much more of them) so the SQL String would be:
rawdata <- sqlQuery(channel, "select YPOS, XPOS, YEAR, MONTH, RANGE, APFEL,
TEMPMIN, TEMPAVG, TEMPMAX, PRECIPITATION, SUNDURATION from DATA where YPOS=1
AND XPOS=182 AND MONTH BETWEEN 1 AND 12 AND RANGE = 1;")
Since I can't use any $xpos oder $ypos (like in Perl) within R, I'm stuck.
I've tried some "xpos" and some 'ypos', some $xpos and some "$xpos" but
those would not work either.
So how can I set and retrieve variables in an SQL String in R?
Kind regards,
John Fitzgerald
______________________________________________
R-help@r-project.org mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
and provide commented, minimal, self-contained, reproducible code.
