Passing Multiple Variable Into SQLDF Statement as parameters of function

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Passing Multiple Variable Into SQLDF Statement as parameters of function

scstrein
Hey guys,

So I'm working with a project where I manage a database within R, and I'm developing a script/function that will automatically run my queries in R depending on the date parameters passed in.

The problem is that when I create variables for the dates, and use those variables in my sqldf statements, R says that there is no such column in the dataframe (which is true!). My question is: how can I pass multiple date variables into my sqldf statements? In particular, the sqldf is supposed to read the date parameter as a character string (its the only way I could get it to work).

This is my code so far:

> StartDate='2010-06-15'
> EndDate='2010-06-22'

> Example=paste("select * from LibDB where Date_Entered =",as.character(StartDate))
> sqldf(Example,verbose=TRUE)

This passes the StartDate in correctly, but in a form where my particular query won't pick up the date in the database. In reality, I want it to act as the following:

> Example=paste("select * from LibDB where Date_Entered ='2010-06-15'")

Eventually, I need to have statements such as:

> Example=paste("select * from LibDB where Date_Entered  between '2010-06-15' and '2010-06-22')

Thanks in advance for any feedback! :)
Reply | Threaded
Open this post in threaded view
|

Re: Passing Multiple Variable Into SQLDF Statement as parameters of function

Gabor Grothendieck
On Wed, Jul 11, 2012 at 11:16 AM, scstrein <[hidden email]> wrote:

> Hey guys,
>
> So I'm working with a project where I manage a database within R, and I'm
> developing a script/function that will automatically run my queries in R
> depending on the date parameters passed in.
>
> The problem is that when I create variables for the dates, and use those
> variables in my sqldf statements, R says that there is no such column in the
> dataframe (which is true!). My question is: how can I pass multiple date
> variables into my sqldf statements? In particular, the sqldf is supposed to
> read the date parameter as a character string (its the only way I could get
> it to work).
>
> This is my code so far:
>
>> StartDate='2010-06-15'
>> EndDate='2010-06-22'
>
>> Example=paste("select * from LibDB where Date_Entered
>> =",as.character(StartDate))
>> sqldf(Example,verbose=TRUE)
>
> This passes the StartDate in correctly, but in a form where my particular
> query won't pick up the date in the database. In reality, I want it to act
> as the following:
>
>> Example=paste("select * from LibDB where Date_Entered ='2010-06-15'")
>
> Eventually, I need to have statements such as:
>
>> Example=paste("select * from LibDB where Date_Entered  between
>> '2010-06-15' and '2010-06-22')
>

See example 5 on the sqldf home page;
http://code.google.com/p/sqldf/#Example_5._Insert_Variables
--
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: Passing Multiple Variable Into SQLDF Statement as parameters of function

jholtman
In reply to this post by scstrein
You have to explicitly add the quotes:

 Example=paste0("select * from LibDB where Date_Entered
='",as.character(StartDate), "'") # notice the single quotes within
the double quotes
> sqldf(Example,verbose=TRUE)

On Wed, Jul 11, 2012 at 11:16 AM, scstrein <[hidden email]> wrote:

> Hey guys,
>
> So I'm working with a project where I manage a database within R, and I'm
> developing a script/function that will automatically run my queries in R
> depending on the date parameters passed in.
>
> The problem is that when I create variables for the dates, and use those
> variables in my sqldf statements, R says that there is no such column in the
> dataframe (which is true!). My question is: how can I pass multiple date
> variables into my sqldf statements? In particular, the sqldf is supposed to
> read the date parameter as a character string (its the only way I could get
> it to work).
>
> This is my code so far:
>
>> StartDate='2010-06-15'
>> EndDate='2010-06-22'
>
>> Example=paste("select * from LibDB where Date_Entered
>> =",as.character(StartDate))
>> sqldf(Example,verbose=TRUE)
>
> This passes the StartDate in correctly, but in a form where my particular
> query won't pick up the date in the database. In reality, I want it to act
> as the following:
>
>> Example=paste("select * from LibDB where Date_Entered ='2010-06-15'")
>
> Eventually, I need to have statements such as:
>
>> Example=paste("select * from LibDB where Date_Entered  between
>> '2010-06-15' and '2010-06-22')
>
> Thanks in advance for any feedback! :)
>
>
> --
> View this message in context: http://r.789695.n4.nabble.com/Passing-Multiple-Variable-Into-SQLDF-Statement-as-parameters-of-function-tp4636147.html
> Sent from the R help mailing list archive at Nabble.com.
>
> ______________________________________________
> [hidden email] 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.



--
Jim Holtman
Data Munger Guru

What is the problem that you are trying to solve?
Tell me what you want to do, not how you want to do it.

______________________________________________
[hidden email] 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.
Reply | Threaded
Open this post in threaded view
|

Re: Passing Multiple Variable Into SQLDF Statement as parameters of function

scstrein
In reply to this post by Gabor Grothendieck
Thanks! For some reason, example 5 in that documentation doesn't work for me.  It runs the query, but I'm getting zero results.  If I substitute the variable with the actual value, I do get the results I want.  Is there a reason for this?
Reply | Threaded
Open this post in threaded view
|

Re: Passing Multiple Variable Into SQLDF Statement as parameters of function

Gabor Grothendieck
On Thu, Jul 12, 2012 at 9:41 AM, scstrein <[hidden email]> wrote:
> Thanks! For some reason, example 5 in that documentation doesn't work for me.
> It runs the query, but I'm getting zero results.  If I substitute the
> variable with the actual value, I do get the results I want.  Is there a
> reason for this?

Read the troubleshooting section and if that still does not address
your problem then contact me.
http://code.google.com/p/sqldf/#Troubleshooting

--
Statistics & Software Consulting
GKX Group, GKX Associates Inc.
tel: 1-877-GKX-GROUP
email: ggrothendieck at gmail.com

______________________________________________
[hidden email] 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.