Hi All, This might be simple question, I need to retrive data for modelling from the databases. Eveytime date values changes so I countnot fix date value in the code, it is required to pass as parameter. When I pass the date as parameter, it throws error. (ERROR: column "start_dt" does not exist Position: 285) My script is as below, please guide me where am I going wrong? All parameters are passed correctly, when start_dt and end_dt are replaced by '2010-11-01' and '2011-01-31' respectively in the query code works fine without any errors. ##################################################################### db_driver <- mydir$db_driver db_jar_file <- mydir$db_jar_file db_server <- mydir$db_server db_server_lgn <- mydir$db_server_lgn db_server_pwd <- mydir$db_server_pwd
library(RJDBC) .jinit(classpath="myClasses.jar", parameters="-Xmx4096m") drv <- JDBC(paste(db_driver, sep = ""), paste(db_jar_file, sep = ""), identifier.quote="`") conn <- dbConnect(drv, paste(db_server, sep = ""), paste(db_server_lgn, sep = ""), paste(db_server_pwd, sep = "")) start_dt <- as.Date('2010-11-01',format="%Y-%m-%d") end_dt <- as.Date('2011-01-31',format="%Y-%m-%d") library(sqldf) target_population <- dbGetQuery(conn, "select distinct a.primary_customer_code as cust_id, a.primary_product_code, a.account_opening_date, b.l4_product_hierarchy_code, b.l5_product_hierarchy_code from account_dim a, product_dim b where a.primary_product_code=b.l5_product_hierarchy_code and a.account_opening_date between start_dt and end_dt") #################################################################### As it is not possible to reproduce error with the above code, I am providing sample example as below with sqldf function using dataframe. date_tm <- as.Date(c('2010-11-01', '2011-11-01','2010-12-01', '2011-01-01', '2011-02-01')) x1 <- c(1,2,3,4,5) x2 <- c(100,200,300,400,500) test_data <- data.frame(x1,x2,date_tm) test_data start_dt <- as.Date('2011-01-01',format="%Y-%m-%d") #Passing as parameter end_dt <- as.Date('2011-02-31',format="%Y-%m-%d") #Passing as parameter library(sqldf) new_data <- sqldf("select * from test_data where date_tm = start_dt") It shows similar error, when date is passed by parameter start_dt (error in statement: no such column: start_dt) [[elided Yahoo spam]] ~Ajit -- View this message in context: http://r.789695.n4.nabble.com/Passing-date-as-parameter-while-retrieving-data-from-database-using-dbGetQuery-tp4390216p4390216.html Sent from the R help mailing list archive at Nabble.com. ______________________________________________ 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.