Add input value from selectinput into reactive SQL statement

shiny
#1

I have following selectInput statement:

selectInput(inputId = "All_Time1",label = 'Date', choices = filterDates, selected = filterDates[1], multiple = FALSE)

With the selection done, I want to create an SQL statement to read data from an SQL database:

Filters <- reactive({
  query <- "select * from dbo.test WHERE data_DT = 'RPL_data_DT'"
  query <- sub('RPL_data_DT', input$All_Time1, query)
  Filters <-   dbGetQuery(con, query)
})

Unfortunately, I am always getting the error "Warning: Error in sub: object 'input' not found".
Why does my reactive function not recognise my input value?

#2

Make sure selectInput is in the UI and the reactive expression in the server, for example:

ui <- shinyUI(
  ...
  selectInput(inputId = "All_Time1",label = 'Date', choices = filterDates,
    selected = filterDates[1], multiple = FALSE
  ...
)

server <- function(input, output) {
  ...
  Filters <- reactive({
    query <- "select * from dbo.test WHERE data_DT = 'RPL_data_DT'"
    query <- sub('RPL_data_DT', input$All_Time1, query)
    Filters <-   dbGetQuery(con, query)
})
  ...
}

shinyApp(ui = ui, server = server)

Note, that server is a function with arguments input and output. This is where input for your reactive expression comes from. From your error message I'm guessing this is missing.

1 Like
#3

Indeed, I had to move the reactive expression into the server function. Now it works, thanks a lot!