Pass a variable value as parameter to sqldf

Question

I have a data set with id and dates. I take the today date into a variable.

today=Sys.date()

Now I want to calculate the latest data in my data set which is less than today date.

Date=sqldf(paste0("SELECT * FROM DATA WHERE MAX(DATE) <",TODAY))

I'm getting error and not able to resolve it.

I hope this is a very-simplified example ... otherwise, that query makes no sense, selecting either all or nothing. (Isn't max only allowed in the result set (left of where)?) — r2evans
– r2evans, Commented Mar 29, 2018 at 7:13

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

You probably need single quotes around the value returned from Sys.Date(). This should work

TODAY <- Sys.Date()
sqldf(paste0("SELECT * FROM DATA",
        " WHERE DATE < '", TODAY, "'",
        " ORDER BY DATE DESC LIMIT 1"))

This would generate the following raw query:

SELECT *
FROM DATA
WHERE DATE < '2018-03-29'
ORDER BY DATE DESC
LIMIT 1

This query will return the most recent record in your data happening before today.

Note that building a SQL string using raw concatenation like is generally bad. But if you are just doing it from your R console for some data analysis, then it should be acceptable.

data

DATA <- data.frame(DATE = c(Sys.Date() + 5:10, Sys.Date() - 5:10))

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Mar 29, 2018 at 6:55

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

vipinder Over a year ago

Than you for the quick response. But I'm getting the below error "Error in rsqlite_send_query(conn@ptr, statement) : misuse of aggregate function max()"

Tim Biegeleisen Over a year ago

@akrun What is the error you see when you run my code?

akrun Over a year ago

It is the same error vipinder mentioned earlier. Looks like you have changed the code. THis code is not yet completed. May be some quotes missing?

Tim Biegeleisen Over a year ago

I'm not in front of an R console. If you can examine the raw output from paste, and if it matches my expected raw query, then this should work.

akrun Over a year ago

I think you forgot one quote. I updated it. Hope you don't mind

|

Collectives™ on Stack Overflow

Pass a variable value as parameter to sqldf

1 Answer 1

data

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

data

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related