[R] Reference a variable inside a string and another for object assingments

[Kenneth Rose]

I tried these two and testquery2 now does the job :) Somehow the str_c
function from the stringr package doesn't work when I run the sqlQuery
function even though the testquery string is the same as testquery2
string. Thanks for the pointers!

fromdate <- c("'2005-01-01 00:00:00'")

testquery <- str_c("SELECT  SP.lokaldatotid, SP.pris FROM
DataIndsamling2.dbo.SpotPriser SP",
                   "WHERE (SP.omraade_id= 0 AND SP.lokaldatotid >=
",fromdate[1],")")

testquery2 <- paste("SELECT  SP.lokaldatotid, SP.pris FROM
DataIndsamling2.dbo.SpotPriser SP",
                    "WHERE (SP.omraade_id= 0 AND SP.lokaldatotid >="
,fromdate[1],")")

I still can't figure out how to assign an existing xts object to a
name from a list or array. I have data that i want to rename to one of
the names i a character vector.

Example:


names <- c("data2", "data3")

data <- xts(11:10, Sys.Date()+1:10)

# The next part is not real code but just so you get the basic idea:

names[1] <- data
remove(data)

data2 should now be assigned to data.


Thanks for your help with the first part!!

/Kenneth


On Thu, Aug 16, 2012 at 10:57 PM, MacQueen, Don <macqueen1 at llnl.gov> wrote:
> I sometimes do this sort of thing with "tricks" like this:
>
>   sql <- "select * from mytable where dt >= 'ADATE'"
>
>   dbGetQuery( con, gsub('ADATE', '2012-06-12 23:14', sql) )
>
>
>
> Or if mydates is a vector of dates stored as a POSIXt object:
>
> for (id in mydates) {
>  dbGetQuery( con,  gsub('ADATE', format(id), sql) )
> }
>
>
> -Don
>
> --
> Don MacQueen
>
> Lawrence Livermore National Laboratory
> 7000 East Ave., L-627
> Livermore, CA 94550
> 925-423-1062
>
>
>
>
>
> On 8/16/12 12:30 PM, "Kenneth Rose" <kennethrose82 at gmail.com> wrote:
>
>>Hi R community
>>
>>I copied a bit of my R code that gets some data from a database. You
>>won't be able to run the code, but I am a beginner so you will
>>probably understand what going on.
>>
>>I would like to make a variable I can refer to inside the sqlQuery.
>>Instead of writing the start date and time (ex SP.lokaldatotid >=
>>'2005-01-01 00:00:00') inside the query I would like to define it in
>>the beginning of the code, so I don't have to fiddle with a lot of
>>dates each time I wan't to change it. I would like to do this for a
>>few of the variables and maybe even make a list/array I can loop
>>through, so I don't have to write the same code multiple times (for
>>SYS and DK1).
>>
>>I have searched for a solution for two days now, but I am not sure
>>what it's called and are probably writing the wrong queries :-)
>>
>>
>>Thank you for your help!
>>
>>Kenneth
>>
>>My code:
>>
>>library(xts)
>>library(RODBC)
>>
>>
>>#Define channnel (i configured my own SYSTEM-DNS, via ODBC)
>>ch <- odbcConnect("DI2")
>>
>>#####################################################################
>>############################## GET DATA    ##########################
>>#####################################################################
>>
>>############################## SYSTEM spot ##########################
>># Hent data fra SQL Server
>>sys <- sqlQuery (ch, paste("SELECT  SP.lokaldatotid, SP.pris FROM
>>DataIndsamling2.dbo.SpotPriser SP",
>>                           "WHERE (SP.omraade_id= 0 AND
>>SP.lokaldatotid >= '2005-01-01 00:00:00')"))
>>#Definer dato og tid kolonne
>>sys$lokaldatotid <- as.POSIXct(sys$lokaldatotid)
>>
>>#Make a XTS object
>>sys_xts <- xts(sys[,-1], order.by=sys[,1])
>>
>># Recalculate data from hours to daily, monthly and yearly averages
>>sys_xts_daily <- apply.daily(sys_xts, FUN=mean)
>>sys_xts_monthly <- apply.monthly(sys_xts, FUN=mean)
>>sys_xts_yearly <- apply.yearly(sys_xts, FUN=mean)
>>
>>
>>############################## DK1 spot #############################
>># Hent data fra SQL Server
>>dk1 <- sqlQuery (ch, paste("SELECT  SP.lokaldatotid, SP.pris FROM
>>DataIndsamling2.dbo.SpotPriser SP",
>>                           "WHERE (SP.omraade_id= 5 AND
>>SP.lokaldatotid >= '2005-01-01 00:00:00')"))
>>#Definer dato og tid kolonne
>>dk1$lokaldatotid <- as.POSIXct(dk1$lokaldatotid)
>>
>>#Lav om til xts object
>>dk1_xts <- xts(dk1[,-1], order.by=dk1[,1])
>>
>>#Data omregnet fra time ->> daglig, måned, årlige gennemsnit
>>dk1_xts_daily <- apply.daily(dk1_xts, FUN=mean)
>>dk1_xts_monthly <- apply.monthly(dk1_xts, FUN=mean)
>>dk1_xts_yearly <- apply.yearly(dk1_xts, FUN=mean)
>>
>>______________________________________________
>>R-help at 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.
>