?paste

e.g.,

sql <- paste("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;",
sep="")

On Mon, May 25, 2009 at 4:26 PM, John Fitzgerald <
john.fitzgerald@internet.de> wrote:

>
> 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<http://www.r-project.org/posting-guide.html>
> and provide commented, minimal, self-contained, reproducible code.
>



-- 
Jim Holtman
Cincinnati, OH
+1 513 646 9390

What is the problem that you are trying to solve?

	[[alternative HTML version deleted]]


